question

smudley avatar image
smudley asked

Update a third column based on comparison of two others?

I have a column of non-unique values that I need to compare to a column of unique values. I then want to update a third column if the value from the non-unique column matched a value in the unique column. ![alt text][1] [1]: /storage/temp/2622-img-one.png Is there a way to do this in SQl 2008? All of the values are in the same table. I imagine I need to update the status column, but I can't figure out how to do the compare to determine the update. Thanks!
sql-server-2008sqlupdate
img-one.png (5.0 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
WITH unique AS ( SELECT DISTINCT value=unique_values FROM my_table ) UPDATE mt SET Status = CASE WHEN u.value is null THEN 'No Match' ELSE 'Match' END FROM my_table mt LEFT JOIN unique u ON u.value = mt.[Non-Unique] ;
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you. That is a MySQL query, or am I mistaken? I will try and see if I can translate that to MS SQL 2008 and post it back up here if I can get it to work.
0 Likes 0 ·
I wrote that using SQL Server syntax and the column names from your example. I wasn't able to test it, but it should work. Let me know if you have trouble.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.