How this Update Query can be made?

Transact-SQL
1

Mates,

I have two tables Table A and Table B.

Table A
Myid Myname Mytype Is_used

100 sid teacher yes
120 jib nurse yes
130 gops actor yes
140 mish solider yes
150 zimbu writer yes
180 mish lover togIve
170 oba sales togIve

(MyId is reffered in the Table B as Prof X id)
Table b
Sl.no Prof1id Prof2id prof3id prof4id prof5id

1 100 120 150 140 130

2 120 150 100 140 130

Select all 'MYId' from table A that is not used in Table B (i.e not reffered in any of the ProfxId columns ) and for those 'MYId' set the value of the coloumn "IS_used" as "togIve"

a update query for above condition is am looking for

Also need another update query for below

Select all 'MYId' from table A that is used in Table B (i.e reffered in any of the ProfxId columns ) and for those 'MYId' set the value of the coloumn "IS_used" as "Yes'

Regards
Mandan

2

Update A set is_used = 'togIve' from
TableA A
where not exists (select 1 from TableB B where a.MyId = B.Prof1Id )
and not exists (select 1 from TableB B where a.MyId = B.Prof2Id )
and not exists (select 1 from TableB B where a.MyId = B.Prof3Id )
and not exists (select 1 from TableB B where a.MyId = B.Prof4Id )
and not exists (select 1 from TableB B where a.MyId = B.Prof5Id )

--select * from
update A set is_used = 'yes' from
TableA A
where exists (select 1 from TableB B where a.MyId = B.Prof1Id )
OR exists (select 1 from TableB B where a.MyId = B.Prof2Id )
OR exists (select 1 from TableB B where a.MyId = B.Prof3Id )
OR exists (select 1 from TableB B where a.MyId = B.Prof4Id )
OR exists (select 1 from TableB B where a.MyId = B.Prof5Id )