CODEWITHSUNDEEP
sql-servert-sqlsql-updatecompare
alexander.skibin
alexander.skibin

Reputation: 133

TSQL update value with subquery

I have 2 tables and want to compare them and modify tableA (set NameMod = 1) if it has different rows.

To compare tables I use: 

 select Id, Name from tableB
 except
 select Id, Name from tableA

And then I want to modify tableA: 

 update tableA Set NameMod = 1
 where exists (
  select Id, Name from tableB
  except
  select Id, Name from tableA
  )

But I can only use EXISTS before the sub-query and in this case it updates all elements in table not different rows.

Upvotes: 2

Views: 152

Answers (1)

gotqn
gotqn

Reputation: 43636

Could you try this:

MERGE TableA AS [Target]
USING TableB AS [Source]
    ON [Target].[ID] = [Source].[ID]
    AND [Target].[Name ] = [Source].[Name]
WHEN NOT MATCHED BY TARGET
    THEN UPDATE SET NameMod = 1;

It is using the MERGE clause.

If you do not like the clause, you can use CTE like this:

;WITH IdsForUpdate ([id]) AS
(
    SELECT DISTINCT Id
    FROM
    (
        select Id, Name from tableB
          except
          select Id, Name from tableA
    ) DS([id], [name])
)
 update tableA 
 Set NameMod = 1
 FROM tableA A
 INNER JOIN IdsForUpdate B
    ON A.[id] =  B.[id];

Upvotes: 2

Related Questions