column update with table join error
A user on IDS 11.50 got SQL error -201 trying an UPDATE ... SET ... FROM ... WHERE join syntax that the SQL Tutorial manual appeared to document. Art Kagel confirmed IDS does not support a FROM clause in UPDATE (the manual page is wrong), and Fernando Nunes identified it as documentation defect IC78586 — that syntax only works on XPS. The suggested workaround is a correlated subquery in the SET clause, with a WHERE ... IN (SELECT ...) filter so non-matching rows aren't overwritten with NULLs; the poster reported this worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IBM Informix Dynamic Server Version 11.50.FC9W1 -- On-Line -- Up 1 days
13:32:04 -- 1595844 Kbytes
I am trying to do a simple column update with a table join and am getting a
'SQL Error (-201): A syntax error has occurred.'
According to the docs it should work:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlt.doc/ids
_sqt_246.htm
Does anyone know why it doesn't work, is this a known bug?
Here is the sql:
update temp_loc
set temp_loc.city_code=jurisdictions.jurisdiction
from jurisdictions,temp_loc
where temp_loc.activity_idx=jurisdictions.activity_idx;
You're correct; it should work. I posted a message on the user groups website
but haven't heard back yet. In the mean time this is a work around that
appears to work: (I added indexes)
drop table jurisdictions;
select le.activity_idx,case
when ro_department in ('AMERICAN FOR PD', 'AMERICAN FORK', 'AMERICAN FORK CITY
PD', 'AMERICAN FORK POLICE DEPARTMENT', 'AMERICANFORK PD') then '01310'
when ro_department = 'BRIGHAM PD' then '08460'
when ro_department = 'BYU PD' then '62470'
when ro_department in ('CEDAR CITY', 'CEDAR PD', 'CEDARY CITY PD','SUU PD')
then '11320'
when ro_department = 'CENTERFIELD POLICE DEPT.' then '11870'
when ro_department = 'CLEARFLIELD PD' then '13850'
when ro_department = 'CLINTON' then '14290'
when ro_department in ('COTTONWODD HEIGHTS PD','COTTONWOOD HEIGHS
PD','COTTONWOOD HEIGHT PD','COTTONWOOD HEIGHTS','COTTOWNWOOD HEIGHT PD') then
'16270'
when ro_department = 'EAST CARBON' then '20890'
when ro_department in ('GRANSTVILLE PD','GRANSVILLE PD','GRANTVILLE PD') then
'31120'
when ro_department = 'GUNNISON CITY POLICE DEPARTMENT' then '32660'
when ro_department = 'HARRIVILLE PD' then '33540'
when ro_department in ('HEBE CITY PD','HEBER CITY PD') then '34200'
when ro_department in ('HURRIANE PD','HURRICANE') then '37170'
when ro_department = 'IVIN PD' then '38710'
when ro_department in ('KAYSVILLE','KAYSVILLE CITY','KAYSVILLE
POLICE','KAYSVILLE POLICE DEPARTMENT') then '40360'
when ro_department in ('LYATON PD','LAYTON','LAYTON CITY PD','LAYTON
D','LATYON PD') then '43660'
when ro_department = 'LAVERKIN PD' then '43440'
when ro_department = 'LEHI' then '44320'
when ro_department = 'LINDON' then '45090'
when ro_department in ('LONGAN PD','LOGAN CITY','LOGAN CITY PD') then '45860'
when ro_department in ('MAPLETON','MAPLETONE PD','MAPLTON PD') then '47950'
when ro_department in ('MIDVALE','MIDVALE CITY PD','MIDVLAE PD') then '49710'
when ro_department = 'MONTECIELLO PD' then '51580'
when ro_department = 'MT PLEASANT PD' then '53010'
when ro_department in ('MURRAY','MURRAY CITY PD','MURRY PD') then '53230'
when ro_department in ('N OGDEN PD','NORTH OGDEN','NORTH ODGEN PD','NORTH OGDN
PD') then '55100'
when ro_department = 'NAVAJO PD' then '53780'
when ro_department = 'NORTH SALT LAKE' then '55210'
when ro_department = 'NORTH SL' then '55210'
when ro_department in ('WEBER PD','ODGEN PD','OGDEN CITY PD','WSU PD') then
'55980'
when ro_department = 'PARK CITY' then '58070'
when ro_department = 'PARK CITYPD' then '58070'
when ro_department = 'PAYSON' then '58730'
when ro_department in ('PEASANT GROVE PD','PLEASANT GROVE','PLESANT GROVE PD')
then '60930'
when ro_department = 'PLEASANT VIEW' then '61040'
when ro_department = 'PRICE' then '62030'
when ro_department = 'PRICE CITY PD' then '62030'
when ro_department = 'PROVO' then '62470'
when ro_department = 'PROVO CITY PD' then '62470'
when ro_department in ('RICHFIELD CITY PD','RICHFIELD CITY POLICE','RICHFIELD
CITY POLICE DEPARTMENT','RICHFIELD CITY POLICE DEPT') then '63570'
when ro_department in ('RIVEDALE PD', 'RIVERDALE CITY PD','RIVERDALE CITY
POLICE') then '64120'
when ro_department = 'SALINA POLICE DEPARTMENT' then '65880'
when ro_department = 'SALT AIRPORT PD' then '67020'
when ro_department = 'SALT LAKE AIRPORT PD' then ''
when ro_department = 'SALT LAKE AIRPORT PD' then ''
when ro_department = 'SALT LAKE ARIPORT PD' then ''
when ro_department in ('SALT LAKE CITY','SALT LAKE CITY P','SALT LAKE
CITYPD','SALT LAKE CIY PD','SALT LAKE CIYT PD','SALT LAKE ICTY PD',
'SALT LAKE PD','SALTALAKE CITY PD','SALT CITY PD', 'SLC PD','STALT LAKE CITY
PD') then '67000'
when ro_department = 'SANDY' then '67440'
when ro_department = 'SANDY CITY PD' then '67440'
when ro_department = 'SANTA CLARE PD' then '67660'
when ro_department in ('SARAGTOGA SPRINGS PD','SARATOAGA SPRINGS PD','SARATOG
SPRINGS PD','SARATOG SPRINGSPD','SARATOGA SPRING PD',
'SARATOGA SPRINGS','SARATOGA SPRINS PD','SARATOGA SRPINGS','SARATOGO SPRINGS
PD','SASRATOGA SPRINGS PD') then '67825'
when ro_department in ('SMITHFIELD', 'SMITHFIELD CITY POLICE
DEPARTMENT','SMITHFIELD POLICE','SMITHFIELD POLICE DEPARTMENT','SMITHFIELDPD')
then '69640'
when ro_department in ('SOTUH JORDAN PD','SOUTH JORDAN PD','SOUTH JORDAN')
then '70850'
when ro_department = 'SOUTH ODGEN PD' then '70960'
when ro_department = 'SOUTH OGDEN CITY PD' then '70960'
when ro_department in ( 'SOUTH LAKE PD','SOTUH SALT LAKE PD','SOUTH SALT
LAKE','SOUTH SALT LAKE CITY PD',
'SOUTH SALT LAKE P','SOUTH SL','SOUTH SL PD','SO SALT LAKE','SO SALT LAKE PD')
then '71070'
when ro_department in ('SPANICH FORK PD','SPANISH FOR PD','SPANISH FORD
PD','SPANISH FORK') then '71290'
when ro_department in ('ST GEORGE PD','ST GEOGE PD','ST GEROGE PD','ST GOERGE
PD','ST. GEORGE PD') then '65330'
when ro_department = 'SUNSET' then '74480'
when ro_department = 'SUSNET PD' then '74480'
when ro_department = 'SYURACUSE PD' then '74810'
when ro_department in ('TALORSVILLE PD','TAYLORSVILL
PD','TAYLORSVILLE','TAYLORSVILLE CITY PD','TAYLORVILLE PD','TAYLROSVILLE
PD','TAYORSVILLE PD') then '75360'
when ro_department = 'TOOELE CITY PD' then '76680'
when ro_department = 'TREMONTON CITY' then '77120'
when ro_department in ('U OF U PD','UNIVERSITY OF UTAH PD','UOFU P','UOFU
PD','UOFU PF','UOFUPD') then '67000'
when ro_department in ('USU PD','USU POLICE','UTAH STATE UNIVERSITY','UTAH
STATE UNIVERSITY PD','UTAH STATE UNIVERSITY POLICE') then '45860'
when ro_department = 'UTE PD' then '79270'
when ro_department in ('UVPD','UTAH VALLEY PD','UVU PD') then '57300'
when ro_department in ('WASHINGTION CITY PD','WASHINGTON CITY PD','WASHINGTON
CITY PD','WASHINTON CITY PD','WASHISNGTON CITY PD') then '81960'
when ro_department = 'WEST JORDON PD' then '82950'
when ro_department in ('WEST VALLEY PD','WEST VALLEY PD','WEST VALLEY
CITY','WEST VALLEY JPD','WEST VALLEY PD','WEST VALLEY PDK',
'WEST VALLY PD','WESTVALLEY OPD','WESTVALLEY PD') then '83470'
when ro_department = 'WILLIARD PD' then '84710'
when ro_department = 'WOOD CROSS PD' then '85370'
else city.code
end jurisdiction
from location loc, le_activity le, crash c, lkup_ut_city city
where le.activity_idx=loc.activity_idx
and le.activity_idx=c.activity_idx
and city.value=trim(substring(ro_department from 1 for length(ro_department) -
3 ))
and length(ro_department) > 3
and city_code is null
into temp jurisdictions;
create index ix_jurisdictions ON jurisdictions
(activity_idx);
drop table temp_loc;
select * from location into temp temp_loc;
create index ix_temp_loc ON temp_loc
(activity_idx);
update temp_loc
set city_code= (select t1.jurisdiction
from jurisdictions t1
where t1.activity_idx=temp_loc.activity_idx);
select * from temp_loc where city_code is not null;
select distinct ro_department, "when ro_department = '" || ro_department || "'then ''" from crash
where length(ro_department) > 3
and ro_department not like "% SO%"
and ro_department not like "% CO%"
and ro_department not like "UHP%"
and ro_department not like "SALT LAKE UNI%"
and substring(ro_department from 1 for length(ro_department) - 3) not in
(select value from lkup_ut_city);
>>> "BEVIS KE
That's weird, I thought that Informix does not support FROM clauses in
UPDATE statements. I do see the referenced pages in the SQL Tutorial
manual, but not in the Syntax Guide. Hmm, in the Tutorial the target table
is mentioned first in the FROM clause, you have it second. I wonder if it
makes a difference.... Nope, I tried that one also and it doesn't work
either so I guess the Tutorial is wrong. Documentation error!
Anyway, try this version without a FROM clause:
update temp_loc
set temp_loc.city_code= (
SELECT jurisdiction
FROM jurisdictions
WHERE temp_loc.activity_idx = jurisdictions.activity_idx )
WHERE activity_idx IN (SELECT activity_idx FROM jurisdictions);
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Feb 2, 2012 at 9:07 AM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
> IBM Informix Dynamic Server Version 11.50.FC9W1 -- On-Line -- Up 1 days
> 13:32:04 -- 1595844 Kbytes>
> I am trying to do a simple column update with a table join and am getting a
> 'SQL Error (-201): A syntax error has occurred.'
>
> According to the docs it should work:
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlt.doc/ids
_sqt_246.htm
>
> Does anyone know why it doesn't work, is this a known bug?
>
> Here is the sql:
> update temp_loc
> set temp_loc.city_code=jurisdictions.jurisdiction
> from jurisdictions,temp_loc
> where temp_loc.activity_idx=jurisdictions.activity_idx;
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6134b8ddd0cd04b7fca5ed
Be VERY CAREFUL. When you update a table using a sub-select if the select
does not return a value then the corresponding row will be updated with a
NULL. If what you intended was for the unmatched rows to not be modified,
then you MUST have a WHERE clause that can eliminate those rows. See the
version of the update that I posted a few minutes ago.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Feb 2, 2012 at 9:54 AM, Bevis Kennedy <bkennedy@utah.gov> wrote:
> You're correct; it should work. I posted a message on the user groups
> website
> but haven't heard back yet. In the mean time this is a work around that
> appears to work: (I added indexes)
> drop table jurisdictions;
> select le.activity_idx,> case
> when ro_department in ('AMERICAN FOR PD', 'AMERICAN FORK', 'AMERICAN FORK
> CITY
> PD', 'AMERICAN FORK POLICE DEPARTMENT', 'AMERICANFORK PD') then '01310'
> when ro_department = 'BRIGHAM PD' then '08460'
> when ro_department = 'BYU PD' then '62470'
> when ro_department in ('CEDAR CITY', 'CEDAR PD', 'CEDARY CITY PD','SUU PD')
> then '11320'
> when ro_department = 'CENTERFIELD POLICE DEPT.' then '11870'
> when ro_department = 'CLEARFLIELD PD' then '13850'
> when ro_department = 'CLINTON' then '14290'
> when ro_department in ('COTTONWODD HEIGHTS PD','COTTONWOOD HEIGHS
> PD','COTTONWOOD HEIGHT PD','COTTONWOOD HEIGHTS','COTTOWNWOOD HEIGHT PD')
> then
> '16270'
> when ro_department = 'EAST CARBON' then '20890'
> when ro_department in ('GRANSTVILLE PD','GRANSVILLE PD','GRANTVILLE PD')
> then
> '31120'
> when ro_department = 'GUNNISON CITY POLICE DEPARTMENT' then '32660'
> when ro_department = 'HARRIVILLE PD' then '33540'
> when ro_department in ('HEBE CITY PD','HEBER CITY PD') then '34200'
> when ro_department in ('HURRIANE PD','HURRICANE') then '37170'
> when ro_department = 'IVIN PD' then '38710'
> when ro_department in ('KAYSVILLE','KAYSVILLE CITY','KAYSVILLE
> POLICE','KAYSVILLE POLICE DEPARTMENT') then '40360'
> when ro_department in ('LYATON PD','LAYTON','LAYTON CITY PD','LAYTON
> D','LATYON PD') then '43660'
> when ro_department = 'LAVERKIN PD' then '43440'
> when ro_department = 'LEHI' then '44320'
> when ro_department = 'LINDON' then '45090'
> when ro_department in ('LONGAN PD','LOGAN CITY','LOGAN CITY PD') then
> '45860'
> when ro_department in ('MAPLETON','MAPLETONE PD','MAPLTON PD') then '47950'
> when ro_department in ('MIDVALE','MIDVALE CITY PD','MIDVLAE PD') then
> '49710'
> when ro_department = 'MONTECIELLO PD' then '51580'
> when ro_department = 'MT PLEASANT PD' then '53010'
> when ro_department in ('MURRAY','MURRAY CITY PD','MURRY PD') then '53230'
> when ro_department in ('N OGDEN PD','NORTH OGDEN','NORTH ODGEN PD','NORTH
> OGDN
> PD') then '55100'
> when ro_department = 'NAVAJO PD' then '53780'
> when ro_department = 'NORTH SALT LAKE' then '55210'
> when ro_department = 'NORTH SL' then '55210'
> when ro_department in ('WEBER PD','ODGEN PD','OGDEN CITY PD','WSU PD') then
> '55980'
> when ro_department = 'PARK CITY' then '58070'
> when ro_department = 'PARK CITYPD' then '58070'
> when ro_department = 'PAYSON' then '58730'
> when ro_department in ('PEASANT GROVE PD','PLEASANT GROVE','PLESANT GROVE
> PD')
> then '60930'
> when ro_department = 'PLEASANT VIEW' then '61040'
> when ro_department = 'PRICE' then '62030'
> when ro_department = 'PRICE CITY PD' then '62030'
> when ro_department = 'PROVO' then '62470'
> when ro_department = 'PROVO CITY PD' then '62470'
> when ro_department in ('RICHFIELD CITY PD','RICHFIELD CITY
> POLICE','RICHFIELD
> CITY POLICE DEPARTMENT','RICHFIELD CITY POLICE DEPT') then '63570'
> when ro_department in ('RIVEDALE PD', 'RIVERDALE CITY PD','RIVERDALE CITY
> POLICE') then '64120'
> when ro_department = 'SALINA POLICE DEPARTMENT' then '65880'
> when ro_department = 'SALT AIRPORT PD' then '67020'
> when ro_department = 'SALT LAKE AIRPORT PD' then ''
> when ro_department = 'SALT LAKE AIRPORT PD' then ''
> when ro_department = 'SALT LAKE ARIPORT PD' then ''
> when ro_department in ('SALT LAKE CITY','SALT LAKE CITY P','SALT LAKE
> CITYPD','SALT LAKE CIY PD','SALT LAKE CIYT PD','SALT LAKE ICTY PD',
> 'SALT LAKE PD','SALTALAKE CITY PD','SALT CITY PD', 'SLC PD','STALT LAKE
> CITY
> PD') then '67000'
> when ro_department = 'SANDY' then '67440'
> when ro_department = 'SANDY CITY PD' then '67440'
> when ro_department = 'SANTA CLARE PD' then '67660'
> when ro_department in ('SARAGTOGA SPRINGS PD','SARATOAGA SPRINGS
> PD','SARATOG
> SPRINGS PD','SARATOG SPRINGSPD','SARATOGA SPRING PD',
> 'SARATOGA SPRINGS','SARATOGA SPRINS PD','SARATOGA SRPINGS','SARATOGO
> SPRINGS
> PD','SASRATOGA SPRINGS PD') then '67825'
> when ro_department in ('SMITHFIELD', 'SMITHFIELD CITY POLICE
> DEPARTMENT','SMITHFIELD POLICE','SMITHFIELD POLICE
> DEPARTMENT','SMITHFIELDPD')
> then '69640'
> when ro_department in ('SOTUH JORDAN PD','SOUTH JORDAN PD','SOUTH JORDAN')
> then '70850'
> when ro_department = 'SOUTH ODGEN PD' then '70960'
> when ro_department = 'SOUTH OGDEN CITY PD' then '70960'
> when ro_department in ( 'SOUTH LAKE PD','SOTUH SALT LAKE PD','SOUTH SALT
> LAKE','SOUTH SALT LAKE CITY PD',
> 'SOUTH SALT LAKE P','SOUTH SL','SOUTH SL PD','SO SALT LAKE','SO SALT LAKE
> PD')
> then '71070'
> when ro_department in ('SPANICH FORK PD','SPANISH FOR PD','SPANISH FORD
> PD','SPANISH FORK') then '71290'
> when ro_department in ('ST GEORGE PD','ST GEOGE PD','ST GEROGE PD','ST
> GOERGE
> PD','ST. GEORGE PD') then '65330'
> when ro_department = 'SUNSET' then '74480'
> when ro_department = 'SUSNET PD' then '74480'
> when ro_department = 'SYURACUSE PD' then '74810'
> when ro_department in ('TALORSVILLE PD','TAYLORSVILL
> PD','TAYLORSVILLE','TAYLORSVILLE CITY PD','TAYLORVILLE PD','TAYLROSVILLE
> PD','TAYORSVILLE PD') then '75360'
> when ro_department = 'TOOELE CITY PD' then '76680'
> when ro_department = 'TREMONTON CITY' then '77120'
> when ro_department in ('U OF U PD','UNIVERSITY OF UTAH PD','UOFU P','UOFU
> PD','UOFU PF','UOFUPD') then '67000'
> when ro_department in ('USU PD','USU POLICE','UTAH STATE UNIVERSITY','UTAH
> STATE UNIVERSITY PD','UTAH STATE UNIVERSITY POLICE') then '45860'
> when ro_department = 'UTE PD' then '79270'
> when ro_department in ('UVPD','UTAH VALLEY PD','UVU PD') then '57300'
> when ro_department in ('WASHINGTION CITY PD','WASHINGTON CITY
> PD','WASHINGTON
> CITY PD','WASHINTON CITY PD','WASHISNGTON CITY PD') then '81960'
> when ro_department = 'WEST JORDON PD' then '82950'
> when ro_department in ('WEST VALLEY PD','WEST VALLEY PD','WEST VALLEY
> CITY','WEST VALLEY JPD','WEST VALLEY PD','WEST VALLEY PDK',
> 'WEST VALLY PD','WESTVALLEY OPD','WESTVALLEY PD') then '83470'
> when ro_department = 'WILLIARD PD' then '84710'
> when ro_department = 'WOO
Thanks Art,
I was hoping one of the developers had stumbled across a new feature. But
alas...
Maybe this was an accidental sneak preview of an upcoming feature.
-Bevis
>>> "Art Kagel" <art.kagel@gmail.com> 2/2/2012 8:14 AM >>>
That's weird, I thought that Informix does not support FROM clauses in
UPDATE statements. I do see the referenced pages in the SQL Tutorial
manual, but not in the Syntax Guide. Hmm, in the Tutorial the target table
is mentioned first in the FROM clause, you have it second. I wonder if it
makes a difference.... Nope, I tried that one also and it doesn't work
either so I guess the Tutorial is wrong. Documentation error!
Anyway, try this version without a FROM clause:
update temp_loc
set temp_loc.city_code= (
SELECT jurisdiction
FROM jurisdictions
WHERE temp_loc.activity_idx = jurisdictions.activity_idx )
WHERE activity_idx IN (SELECT activity_idx FROM jurisdictions);
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Feb 2, 2012 at 9:07 AM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
> IBM Informix Dynamic Server Version 11.50.FC9W1 -- On-Line -- Up 1 days
> 13:32:04 -- 1595844 Kbytes>
> I am trying to do a simple column update with a table join and am getting a
> 'SQL Error (-201): A syntax error has occurred.'
>
> According to the docs it should work:
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlt.doc/ids
_sqt_246.htm
>
> Does anyone know why it doesn't work, is this a known bug?
>
> Here is the sql:
> update temp_loc
> set temp_loc.city_code=jurisdictions.jurisdiction
> from jurisdictions,temp_loc
> where temp_loc.activity_idx=jurisdictions.activity_idx;
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6134b8ddd0cd04b7fca5ed
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
In fact it's a doc bug.
It's already referenced as IC78586.
http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IC78586
That syntax only works for XPS.
Regards
On Thu, Feb 2, 2012 at 3:26 PM, Bevis Kennedy <bkennedy@utah.gov> wrote:
> Thanks Art,
>
> I was hoping one of the developers had stumbled across a new feature. But
> alas...
> Maybe this was an accidental sneak preview of an upcoming feature.
>
> -Bevis
>
> >>> "Art Kagel" <art.kagel@gmail.com> 2/2/2012 8:14 AM >>>
> That's weird, I thought that Informix does not support FROM clauses in
> UPDATE statements. I do see the referenced pages in the SQL Tutorial
> manual, but not in the Syntax Guide. Hmm, in the Tutorial the target table
> is mentioned first in the FROM clause, you have it second. I wonder if it
> makes a difference.... Nope, I tried that one also and it doesn't work
> either so I guess the Tutorial is wrong. Documentation error!
>
> Anyway, try this version without a FROM clause:
>
> update temp_loc
> set temp_loc.city_code= (
>
> SELECT jurisdiction>
> FROM jurisdictions
>
> WHERE temp_loc.activity_idx = jurisdictions.activity_idx )
> WHERE activity_idx IN (SELECT activity_idx FROM jurisdictions);
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Thu, Feb 2, 2012 at 9:07 AM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
>
> > IBM Informix Dynamic Server Version 11.50.FC9W1 -- On-Line -- Up 1 days
> > 13:32:04 -- 1595844 Kbytes> >
> > I am trying to do a simple column update with a table join and am
> getting a
> > 'SQL Error (-201): A syntax error has occurred.'
> >
> > According to the docs it should work:
> >
> >
>
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlt.doc/ids
_sqt_246.htm
> >
> > Does anyone know why it doesn't work, is this a known bug?
> >
> > Here is the sql:
> > update temp_loc
> > set temp_loc.city_code=jurisdictions.jurisdiction
> > from jurisdictions,temp_loc
> > where temp_loc.activity_idx=jurisdictions.activity_idx;
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6134b8ddd0cd04b7fca5ed
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf3074d2e8992c2704b7fcecb9
Thank you again Art,
I tried your sql and it made huge difference. It's a good thing we were only
working with temp tables.
Bevis
>>> "Art Kagel" <art.kagel@gmail.com> 2/2/2012 8:18 AM >>>
Be VERY CAREFUL. When you update a table using a sub-select if the select
does not return a value then the corresponding row will be updated with a
NULL. If what you intended was for the unmatched rows to not be modified,
then you MUST have a WHERE clause that can eliminate those rows. See the
version of the update that I posted a few minutes ago.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Feb 2, 2012 at 9:54 AM, Bevis Kennedy <bkennedy@utah.gov> wrote:
> You're correct; it should work. I posted a message on the user groups
> website
> but haven't heard back yet. In the mean time this is a work around that
> appears to work: (I added indexes)
> drop table jurisdictions;
> select le.activity_idx,> case
> when ro_department in ('AMERICAN FOR PD', 'AMERICAN FORK', 'AMERICAN FORK
> CITY
> PD', 'AMERICAN FORK POLICE DEPARTMENT', 'AMERICANFORK PD') then '01310'
> when ro_department = 'BRIGHAM PD' then '08460'
> when ro_department = 'BYU PD' then '62470'
> when ro_department in ('CEDAR CITY', 'CEDAR PD', 'CEDARY CITY PD','SUU PD')
> then '11320'
> when ro_department = 'CENTERFIELD POLICE DEPT.' then '11870'
> when ro_department = 'CLEARFLIELD PD' then '13850'
> when ro_department = 'CLINTON' then '14290'
> when ro_department in ('COTTONWODD HEIGHTS PD','COTTONWOOD HEIGHS
> PD','COTTONWOOD HEIGHT PD','COTTONWOOD HEIGHTS','COTTOWNWOOD HEIGHT PD')
> then
> '16270'
> when ro_department = 'EAST CARBON' then '20890'
> when ro_department in ('GRANSTVILLE PD','GRANSVILLE PD','GRANTVILLE PD')
> then
> '31120'
> when ro_department = 'GUNNISON CITY POLICE DEPARTMENT' then '32660'
> when ro_department = 'HARRIVILLE PD' then '33540'
> when ro_department in ('HEBE CITY PD','HEBER CITY PD') then '34200'
> when ro_department in ('HURRIANE PD','HURRICANE') then '37170'
> when ro_department = 'IVIN PD' then '38710'
> when ro_department in ('KAYSVILLE','KAYSVILLE CITY','KAYSVILLE
> POLICE','KAYSVILLE POLICE DEPARTMENT') then '40360'
> when ro_department in ('LYATON PD','LAYTON','LAYTON CITY PD','LAYTON
> D','LATYON PD') then '43660'
> when ro_department = 'LAVERKIN PD' then '43440'
> when ro_department = 'LEHI' then '44320'
> when ro_department = 'LINDON' then '45090'
> when ro_department in ('LONGAN PD','LOGAN CITY','LOGAN CITY PD') then
> '45860'
> when ro_department in ('MAPLETON','MAPLETONE PD','MAPLTON PD') then '47950'
> when ro_department in ('MIDVALE','MIDVALE CITY PD','MIDVLAE PD') then
> '49710'
> when ro_department = 'MONTECIELLO PD' then '51580'
> when ro_department = 'MT PLEASANT PD' then '53010'
> when ro_department in ('MURRAY','MURRAY CITY PD','MURRY PD') then '53230'
> when ro_department in ('N OGDEN PD','NORTH OGDEN','NORTH ODGEN PD','NORTH
> OGDN
> PD') then '55100'
> when ro_department = 'NAVAJO PD' then '53780'
> when ro_department = 'NORTH SALT LAKE' then '55210'
> when ro_department = 'NORTH SL' then '55210'
> when ro_department in ('WEBER PD','ODGEN PD','OGDEN CITY PD','WSU PD') then
> '55980'
> when ro_department = 'PARK CITY' then '58070'
> when ro_department = 'PARK CITYPD' then '58070'
> when ro_department = 'PAYSON' then '58730'
> when ro_department in ('PEASANT GROVE PD','PLEASANT GROVE','PLESANT GROVE
> PD')
> then '60930'
> when ro_department = 'PLEASANT VIEW' then '61040'
> when ro_department = 'PRICE' then '62030'
> when ro_department = 'PRICE CITY PD' then '62030'
> when ro_department = 'PROVO' then '62470'
> when ro_department = 'PROVO CITY PD' then '62470'
> when ro_department in ('RICHFIELD CITY PD','RICHFIELD CITY
> POLICE','RICHFIELD
> CITY POLICE DEPARTMENT','RICHFIELD CITY POLICE DEPT') then '63570'
> when ro_department in ('RIVEDALE PD', 'RIVERDALE CITY PD','RIVERDALE CITY
> POLICE') then '64120'
> when ro_department = 'SALINA POLICE DEPARTMENT' then '65880'
> when ro_department = 'SALT AIRPORT PD' then '67020'
> when ro_department = 'SALT LAKE AIRPORT PD' then ''
> when ro_department = 'SALT LAKE AIRPORT PD' then ''
> when ro_department = 'SALT LAKE ARIPORT PD' then ''
> when ro_department in ('SALT LAKE CITY','SALT LAKE CITY P','SALT LAKE
> CITYPD','SALT LAKE CIY PD','SALT LAKE CIYT PD','SALT LAKE ICTY PD',
> 'SALT LAKE PD','SALTALAKE CITY PD','SALT CITY PD', 'SLC PD','STALT LAKE
> CITY
> PD') then '67000'
> when ro_department = 'SANDY' then '67440'
> when ro_department = 'SANDY CITY PD' then '67440'
> when ro_department = 'SANTA CLARE PD' then '67660'
> when ro_department in ('SARAGTOGA SPRINGS PD','SARATOAGA SPRINGS
> PD','SARATOG
> SPRINGS PD','SARATOG SPRINGSPD','SARATOGA SPRING PD',
> 'SARATOGA SPRINGS','SARATOGA SPRINS PD','SARATOGA SRPINGS','SARATOGO
> SPRINGS
> PD','SASRATOGA SPRINGS PD') then '67825'
> when ro_department in ('SMITHFIELD', 'SMITHFIELD CITY POLICE
> DEPARTMENT','SMITHFIELD POLICE','SMITHFIELD POLICE
> DEPARTMENT','SMITHFIELDPD')
> then '69640'
> when ro_department in ('SOTUH JORDAN PD','SOUTH JORDAN PD','SOUTH JORDAN')
> then '70850'
> when ro_department = 'SOUTH ODGEN PD' then '70960'
> when ro_department = 'SOUTH OGDEN CITY PD' then '70960'
> when ro_department in ( 'SOUTH LAKE PD','SOTUH SALT LAKE PD','SOUTH SALT
> LAKE','SOUTH SALT LAKE CITY PD',
> 'SOUTH SALT LAKE P','SOUTH SL','SOUTH SL PD','SO SALT LAKE','SO SALT LAKE
> PD')
> then '71070'
> when ro_department in ('SPANICH FORK PD','SPANISH FOR PD','SPANISH FORD
> PD','SPANISH FORK') then '71290'
> when ro_department in ('ST GEORGE PD','ST GEOGE PD','ST GEROGE PD','ST
> GOERGE
> PD','ST. GEORGE PD') then '65330'
> when ro_department = 'SUNSET' then '74480'
> when ro_department = 'SUSNET PD' then '74480'
> when ro_department = 'SYURACUSE PD' then '74810'
> when ro_department in ('TALORSVILLE PD','TAYLORSVILL
> PD','TAYLORSVILLE','TAYLORSVILLE CITY PD','TAYLORVILLE PD','TAYLROSVILLE
> PD','TAYORSVILLE PD') then '75360'
> when ro_department = 'TOOELE CITY PD' then '76680'
> when ro_department = 'TREMONTON CITY' then '77120'
> when ro_department in ('U OF U PD','UNIVERSITY OF UTAH PD','UOFU P','UOFU
> PD','UOFU PF','UOFUPD') then '67000'
> when ro_department in ('USU PD','USU POLICE','UTAH STATE UNIVERSITY','UTAH
> STATE UNIVERSITY PD','UTAH STATE UNIVERSITY POLICE') then '45860'
> when ro_department = 'UTE PD' then '79270'
> when ro_department in ('UVPD','UTAH VALLEY PD','UVU PD') then '57300'
> when ro_department in ('WASHINGTION CITY PD','WASHINGTON CITY
> PD','WASHINGTON
> CITY PD','WASHINTON CITY PD','WASHISNGTON CITY PD') then '81960'
> when ro_department = 'WEST JORDON PD' then '82950'
> when ro_department in ('WEST VALLEY PD','WEST VALLEY
Yup. Might be something that didn't make it out of QA and we may see it in
12.10 when that hits Beta testing in a few weeks. Don't know.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Feb 2, 2012 at 10:26 AM, Bevis Kennedy <bkennedy@utah.gov> wrote:
> Thanks Art,
>
> I was hoping one of the developers had stumbled across a new feature. But
> alas...
> Maybe this was an accidental sneak preview of an upcoming feature.
>
> -Bevis
>
> >>> "Art Kagel" <art.kagel@gmail.com> 2/2/2012 8:14 AM >>>
> That's weird, I thought that Informix does not support FROM clauses in
> UPDATE statements. I do see the referenced pages in the SQL Tutorial
> manual, but not in the Syntax Guide. Hmm, in the Tutorial the target table
> is mentioned first in the FROM clause, you have it second. I wonder if it
> makes a difference.... Nope, I tried that one also and it doesn't work
> either so I guess the Tutorial is wrong. Documentation error!
>
> Anyway, try this version without a FROM clause:
>
> update temp_loc
> set temp_loc.city_code= (
>
> SELECT jurisdiction>
> FROM jurisdictions
>
> WHERE temp_loc.activity_idx = jurisdictions.activity_idx )
> WHERE activity_idx IN (SELECT activity_idx FROM jurisdictions);
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Thu, Feb 2, 2012 at 9:07 AM, BEVIS KENNEDY <bkennedy@utah.gov> wrote:
>
> > IBM Informix Dynamic Server Version 11.50.FC9W1 -- On-Line -- Up 1 days
> > 13:32:04 -- 1595844 Kbytes> >
> > I am trying to do a simple column update with a table join and am
> getting a
> > 'SQL Error (-201): A syntax error has occurred.'
> >
> > According to the docs it should work:
> >
> >
>
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlt.doc/ids
_sqt_246.htm
> >
> > Does anyone know why it doesn't work, is this a known bug?
> >
> > Here is the sql:
> > update temp_loc
> > set temp_loc.city_code=jurisdictions.jurisdiction
> > from jurisdictions,temp_loc
> > where temp_loc.activity_idx=jurisdictions.activity_idx;
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6134b8ddd0cd04b7fca5ed
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340f9d228d3d04b7fd26f0
I think what you have found is a page which was left from the version 8
(XPS)
version. XPS did allow update joins.
While update joins are supported, you can achieve the same result using the
MERGE statement. How about the following:
MERGE INTO t_target AS t USING t_source AS s
ON t.col_a = s.col_a
WHEN MATCHED THEN UPDATE
SET t.col_b = t.col_b + s.col_b ;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic09636.gif)
ids-bounces@iiug.org wrote on 02/02/2012 07:50:06 AM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 02/02/2012 07:52 AM
> Subject: Re: column update with table join error [26134]
> Sent by: ids-bounces@iiug.org
>
> Yup. Might be something that didn't make it out of QA and we may see it
in
> 12.10 when that hits Beta testing in a few weeks. Don't know.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated
nor
> those of the entities themselves.
>
> On Thu, Feb 2, 2012 at 10:26 AM, Bevis Kennedy <bkennedy@utah.gov> wrote:
>
> > Thanks Art,
> >
> > I was hoping one of the developers had stumbled across a new feature.
But
> > alas...
> > Maybe this was an accidental sneak preview of an upcoming feature.
> >
> > -Bevis
> >
> > >>> "Art Kagel" <art.kagel@gmail.com> 2/2/2012 8:14 AM >>>
> > That's weird, I thought that Informix does not support FROM clauses in
> > UPDATE statements. I do see the referenced pages in the SQL Tutorial
> > manual, but not in the Syntax Guide. Hmm, in the Tutorial the target
table
> > is mentioned first in the FROM clause, you have it second. I wonder if
it
> > makes a difference.... Nope, I tried that one also and it doesn't work
> > either so I guess the Tutorial is wrong. Documentation error!
> >
> > Anyway, try this version without a FROM clause:
> >
> > update temp_loc
> > set temp_loc.city_code= (
> >
> > SELECT jurisdiction> >
> > FROM jurisdictions
> >
> > WHERE temp_loc.activity_idx = jurisdictions.activity_idx )
> > WHERE activity_idx IN (SELECT activity_idx FROM jurisdictions);
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
nor
> > those of the entities themselves.
> >
> > On Thu, Feb 2, 2012 at 9:07 AM, BEVIS KENNEDY <bkennedy@utah.gov>
wrote:
> >
> > > IBM Informix Dynamic Server Version 11.50.FC9W1 -- On-Line -- Up 1days
> > > 13:32:04 -- 1595844 Kbytes
> > >
> > > I am trying to do a simple column update with a table join and am
> > getting a
> > > 'SQL Error (-201): A syntax error has occurred.'
> > >
> > > According to the docs it should work:
> > >
> > >
> >
> >
> >
> http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/
> com.ibm.sqlt.doc/ids_sqt_246.htm
> > >
> > > Does anyone know why it doesn't work, is this a known bug?
> > >
> > > Here is the sql:
> > > update temp_loc
> > > set temp_loc.city_code=jurisdictions.jurisdiction
> > > from jurisdictions,temp_loc
> > > where temp_loc.activity_idx=jurisdictions.activity_idx;
> > >
> > >
> > >
> > >
> >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --90e6ba6134b8ddd0cd04b7fca5ed
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340f9d228d3d04b7fd26f0
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>