Red Devil Red Devil - 1 month ago 5
SQL Question

Updating third table where id didnot match from other 2 table

I have 3 tables

Table A

ID Remark
1 NULL
2 Null
4 Null


Table B

ID Remark
1 Null
2 Null
4 Null


Table C

ID
1
2
3


I want to update Table B Remark by joining Table A and Table C where records in Table A does not exist in Table C.

So final Output will look like

Table B

ID Remark
1 Null
2 Null
4 Invalid Entry


I have tried so far below query:

Update TableB set Remark='Invalid'
where not exists ( select ID from TableA join TableC on TableA.ID=TableC.ID)


But I think there is some problem. Can someone help me in this.

Answer Source

You forgot to connect TableB to the exists

Update TabB
set Remark='Invalid'
from TableB TabB
where not exists 
(
select 1 
from TableA 
join TableC 
on  TableA.ID=TableC.ID 
where TabB.id = TableA.id
)