Updating a column based off records from two tables matching?

We’re using SQL Server 2016. I have two tables, DEAD and QUETABLE. However, QUETABLE also has a column called DEAD. This is going to sound confusing, but I’ll try to make it make sense. What I need to do, is set the column (DEAD) in QUETABLE to the value YES if the column WEBSITE in QUETABLE matches the same named column WEBSITE in the table DEAD.

(The DEAD table is just filled with dead websites, QUETABLE might have some that are dead, marking it will let us know)

In a magical world, something like this would work:

UPDATE QUETABLE 
SET [DEAD] = 'YES' 
WHERE
    SELECT de.website, de.[city], de.[state], de.[zip], de.[new-ind], de.phone
    FROM [QUETABLE] de
    WHERE EXISTS (SELECT website, company, zip
                  FROM dead d2
                  WHERE d2.website = de.website OR d2.state = de.state OR d2.zip = de.zip)

But obviously that can’t happen, and I can’t really figure out how to do this.

Answer

I think you simply want:

UPDATE de
    SET [DEAD] = 'YES'
FROM [QUETABLE] de
WHERE EXISTS (SELECT website, company, zip
              FROM dead d2
              WHERE d2.website = de.website OR d2.state = de.state OR d2.zip = de.zip
             );

Advertisement

Answer