UPDATE based on if value exist in another table

Question

I have two tables

Table A

Number
111       
222       
333       
444       

Table B

Number    Another
111       AAA
222       BBB
666       CCC
777       DDD

What I am would like to do, is apply an UPDATE statement conditional on whether the "Number" value in Table B exist in Table A. So the table would end up looking something like this.

Number    Another
111       ZZZ
222       ZZZ
666       CCC
777       DDD

I know I need to use an UPDATE query and probably some kind of JOIN, but I am not sure on the syntax.

Any help greatly appreciated.

Answer 1

Yes. You need to update using a join like this:

update t2
set t2.Another = 'ZZZ'
from table1 t1
join table2 t2 on t1.Number = t2.Number

Answer 2

You can use exists also.

Query

update t1
set t1.[Another] = 'ZZZ'
from [TableB] t1
where exists(
    select 1 from [TableA] t2
    where t1.[Number] = t2.[Number]
);