IDS Forum

Re: column update with table join error

Posted By: Art Kagel
Date: Thursday, 2 February 2012, at 10:18 a.m.

In Response To: Re: column update with table join error (Bevis Kennedy)

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 = '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 KENNEDY" <bkennedy@utah.gov> 2/2/2012 7:07 AM >>>
> 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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--90e6ba6e8bacb834a204b7fcb52e

Messages In This Thread

• column update with table join error
  BEVIS KENNEDY -- Thursday, 2 February 2012, at 9:07 a.m.
  • Re: column update with table join error
    Bevis Kennedy -- Thursday, 2 February 2012, at 9:54 a.m.
    • Re: column update with table join error
      Art Kagel -- Thursday, 2 February 2012, at 10:18 a.m.
      • Re: column update with table join error
        Bevis Kennedy -- Thursday, 2 February 2012, at 10:39 a.m.
  • Re: column update with table join error
    Art Kagel -- Thursday, 2 February 2012, at 10:14 a.m.
    • Re: column update with table join error
      Bevis Kennedy -- Thursday, 2 February 2012, at 10:26 a.m.
      • Re: column update with table join error
        Fernando Nunes -- Thursday, 2 February 2012, at 10:34 a.m.
      • Re: column update with table join error
        Art Kagel -- Thursday, 2 February 2012, at 10:50 a.m.
        • Re: column update with table join error
          John Miller iii -- Friday, 3 February 2012, at 2:01 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.