how to update using nested query in SQL

Question

I have a query that runs on a table, and pulls out 2 fields. I want to take them, and using UPDATE sentence, to use their values as WHERE and SET.

For example:

select name, address from xxx.....

I want to take the name & address and do this

update yyy 
set fullname=(name I pulled before) 
where address=(address I pulled before)

thanks

Answer 1

Give this a try

Update t
Set t.yyyy = q.Name
From TableToUpdate t
Join AddressTable q on q.Address = t.Address

This assumes that Address field (which you are joining on) is a one to one relationship with the Address field in the table you are updating

This can also be written

Update TableToUpdate
Set yyyy = q.Name
From AddressTable q
WHERE q.Address = TableToUpdate.Address

Since the update table is accessible in the FROM/WHERE clauses, except it cannot be aliased.