SQL - as a batch update, given the selection results

Question

I have a select statement that joins several tables and captures some information. I would like to update all records in one of these tables (found in the list) with the information contained in select. The selection is as follows:

SELECT account.id document.id FROM customer INNER JOIN account ON (customer.firstname = account.firstname AND customer.lastname = account.lastname AND customer.phone = account.phone) INNER JOIN document ON customer.id = document.customerid WHERE document.accountid IS NULL; 

In English, the document may be owned by customers and accounts. I am looking for account entries that match client accounts where the document belongs to the client but not the account.

Now I can manually view the results and run this:

 UPDATE document SET accountid = /*account.id*/ WHERE id = /*document.id*/; 

which works as I would like, but there is a decent amount of records that match my query, and I would like to do it in one expression, if I could.

Answer 1

 UPDATE document, account, customer SET documnet.accountid = account.id WHERE (customer.firstname = account.firstname AND customer.lastname = account.lastname AND customer.phone = account.phone) AND customer.id = document.customerid AND document.accountid IS NULL; 

It should do it all in one go.

Answer 2

A more ANSI-compliant solution would be:

 Update document Set accountid = ( Select Min( A1.id ) From customer As C1 Join account As A1 On A1.firstname = C1.firstname And A1.lastname = C1.lastname And A1.phone = C1.phone Where C1.id = document.Id ) Where accountid Is Null 

I use Min( A1.id ) to ensure that I get a maximum of one account.id for this customer information. Joining the Update clause is not officially supported by the SQL specification because it creates ambiguity in updating when the same row can be updated with different values.