How to compare and match two select statements
-
I have select statement (lets just call it select A) and another select statement (lets just call it select B). First, I only want to match a field (zipcodes) from select A to select B and deal with only these records. Second, with the fields that match, I want to compare the rest of the fields from select A to select B. If any of the fields returned from select A is different from select B or vice versa then update select B fields with select A fields.
Does anybody have any ideas how to start this? Your help is much appreciated!
-
Plese post :
-DDL
-Sample data
-expected results
-
select zip, subdistrict, city, state from Feed1
select zip, subdistrict, city, state from Zips
So if zip field from Feed1 matches with zip field from Zips then go ahead and check if subdistrict, city and state match from Feed1 to Zips. If they do then do nothing, if any of the fields do not match then update the four fields in Zips with the ones from Feed1.
Don't expect any results just updates. Let me know if you need more information. Thanks for the help.
-
UPDATE Feed1
SET subdistrict = Zips.subdistrict,
city = Zips.city , state = Zips.state
FROM Feed1 JOIN Zips
ON Feed1.zip=Zips.Zip
AND (
Feed1.subdistrict<>Zips.subdistrict
OR
Feed1.city<>Zips.city OR
Feed1.state <>Zips.state
)
Kindest Regards,Amit Lohia
-
Thank you very much! I think this help me out!
-
Watch out for NULLs on those <> comparisons though
* Noel -
Yes please watch out. I have learned my hardway. Thanks noeld
Kindest Regards,Amit Lohia
-
what happened?
-
John,
If the columns that are being compared with <> can contain null values you will need to account for that also like:
where
col1 <> col2
or
(col1 is null and col2 is not null )
or
(col2 is null and col1 is not null )
* Noel -
where
COALESCE(col1,'') <> COALESCE(col2,'')
Kindest Regards,Amit Lohia
-
that is another way but may preclude the use of indexes
* Noel -
correct
Kindest Regards,Amit Lohia
-
Here's an alternative way that may be easier to read, and also avoiding negations. Of course one has to look at the actual queryplans (and possibly i/o statistics) in order to see which is to prefer.
update f
set f.subdistrict =
case when f.subdistrict = z.subdistrict
then f.subdistrict else z.subdistrict
end,
f.city =
case when f.city = z.city
then f.city else z.city
end,
f.state =
case when f.state = z.state
then f.state else z.state
end
from feed1 f
join zips z
on f.zip = z.zip
Basically you can use CASE for each column to decide if you want the 'old' or the 'new' value to be set. It also avoids complicated OR's which tend to be difficult to read as well as write correctly (and possibly also demands more complex plans)
/Kenneth
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply