Update query 1

[vladk]

Hi, I have two tables, containing info about zip codes. The old table (ZIP_CITY_STATE) and the new table (NEW_ZIP). I need to compare them and for any ZipCode and the corresponding CityStateKey (it makes the unique key), which is no longer on the new table, populate InactivateDate on the old table.

I already have select query, which identifies such a row on the old table, I just need to transform it to update query. Below is the select query.

Thank you!!

SELECT ZIP_CITY_STATE.ID,
ZIP_CITY_STATE.ZipCode,
ZIP_CITY_STATE.CityStateKey
FROM ZIP_CITY_STATE LEFT JOIN NEW_ZIP
ON (ZIP_CITY_STATE.CityStateKey=NEW_ZIP.CityStateKey)
AND (ZIP_CITY_STATE.ZipCode=NEW_ZIP.ZipCode)
WHERE NEW_ZIP.ZipCode IS null AND NEW_ZIP.CityStateKey IS null;

 

[PHV]

Something like this ?
UPDATE ZIP_CITY_STATE
SET InactivateDate = Date()
WHERE ID IN (SELECT ID FROM ZIP_CITY_STATE O LEFT JOIN NEW_ZIP N ON O.CityStateKey=N.CityStateKey AND O.ZipCode=N.ZipCode WHERE N.ZipCode IS NULL)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vladk]

Hi, PHV!

Your query works like a charm. The only thing I changed was:
...(SELECT O.ID FROM ZIP_CITY_STATE ...

PHV, thank you for your help. You saved lot of time for me.

Thank you again

vladk