T-SQL query to remove non matching value from ColumnA where value doesn't exist in ColumnB

Question

I have two databases, with the following tables:

DatabaseA TableA ColumnA (varChar(10)

DatabaseB TableB ColumnB (varChar(10)

I need a query that:

• Ignores NULLs or empty strings
• Finds rows where the value of ColumnA does not exist in columnB
  
  • In this case, replaces the value of the non matching row in ColumnA with '' (empty string)

This is in a MS SQL Server 2008 environment.

Answer 1

You can do this with a LEFT OUTER JOIN as shown below:

UPDATE TableA
SET columnA = ''
FROM
    TableA 
    LEFT JOIN TableB ON TableA.columnA = TableB.columnB
WHERE
    TableA.columnA IS NOT NULL AND TableA.columnA <> '' AND
    TableB.columnB IS NULL;

Answer 2

UPDATE TableA 
SET ColumnA = ''
WHERE  ColumnA IS NOT NULL 
  AND ColumnA <> '' 
  AND NOT EXISTS
     (
     SELECT *
     FROM TableB WHERE TableB.ColumnB = TableA.ColumnA
     )