how to update using nested query in SQL

I have a query that runs on a table, and pulls out 2 fields. I want to take them, and using UPDATE sentence, to开发者_StackOverflow 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

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.

If you're using SQL Server 2005 or up (which you didn't specify ....), you can use a Common Table Expression (CTE):

;WITH UpdateData AS
(
    SELECT
          FullName,
          Address
    FROM 
          dbo.SomeTableYouUse
    WHERE
          (some critiera)
)
UPDATE dbo.yyy 
SET fullname = ud.FullName
FROM UpdateData ud
WHERE address = ud.Address

Inside your CTE, you can figure out how to determine your FullName and Address, and the CTE is sort of an "inline" view which is valid for the next statement only - in this case, for the UPDATE statement.