SQL - How to batch update given the results of a select

Question

I've got a select statement that joins a couple of tables and grabs some information. I'd like to updated all of the records on one of those tables (found in the select) with information contained in the select. The select looks like this:

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, a document can belong to customers and accounts. I'm looking for the account records that match customer records where the document belongs to the customer, but not the account.

Now, I can manually go through the results and run this:

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

which works as I would like, but there's a decent amount of records that match my query and I'd like to do it in a single statement 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;

That should do it all in one go

Answer 2

A more ANSI compliant solution would be the following:

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'm using Min( A1.id ) to ensure that I get at most one account.id for the given customer information. A join in an Update clause is not officially supported by the SQL specification because it creates ambiguities in the Update when the same row might be updated with different values.