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.
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];