Help with UPDATE QUERY please 1

[foxup]

Hi, I have this QUERY which is not updating the records like it's supposed to. For some reason, it's only updateing all the records with the same value and that's not what should be happeing. here is my query:


UPDATE MST1107 SET STATE=PIC_CITY.PROV WHERE EMPTY(STATE) AND CALLTYPE='800' AND SUBSTR(TEL,1,7) IN (SELECT NPANXX FROM PIC_CITY)


please help.


Thanks,
J

 

[TamarGranor]

You need to add the PIC_CITY table to a FROM clause and use a JOIN (or an implied JOIN in WHERE) to make this work. Right now, it's just using the current value of PIC_CITY.Prov.

Try it like this:

UPDATE MST1107 ;
   FROM PIC_CITY ;
   SET STATE=PIC_CITY.PROV ;
   WHERE EMPTY(STATE) AND CALLTYPE='800' AND ;
       SUBSTR(TEL,1,7) = PIC_CITY.NPANXX

Tamar

 

[foxup]

Tamar the Great!! Star for U. Thank You.