Update table with values from another table

I have these tables:

customer:
    customer_id vat_number
    =========== ==========
              1 ES-0000001
              2 ES-0000002
              3 ES-0000003


invoice:
    invoice_id customer_id vat_number
    ========== =========== ==========
           100           1 NULL
           101           3 NULL
           102           3 NULL
           103           2 NULL
           104           3 NULL
           105           1 NULL

I want to fill the NULL values at invoice.vat_number w开发者_Go百科ith the current values from customer.vat_number. Is it possible to do it with a single SQL statement?

What I have so far triggers a syntax error:

UPDATE invoice
SET vat_number=cu.vat_number /* Syntax error around here */
FROM invoice iv
INNER JOIN customer cu ON iv.customer_id=cu.customer_id
WHERE invoice.invoice_id=iv.invoice_id;

Using MySQL, ANSI-92 JOIN syntax:

UPDATE INVOICE
  JOIN CUSTOMER ON CUSTOMER.customer_id = INVOICE.customer_id
   SET vat_number = CUSTOMER.vat_number  
 WHERE INVOICE.vat_number IS NULL

Using MySQL, ANSI-89 JOIN syntax:

UPDATE INVOICE, CUSTOMER 
   SET INVOICE.vat_number = CUSTOMER.vat_number  
 WHERE CUSTOMER.customer_id = INVOICE.customer_id
   AND INVOICE.vat_number IS NULL

For more info, see the MySQL UPDATE documentation. This is MySQL specific UPDATE statement syntax, not likely to be supported on other databases.

UPDATE invoice i, customer cu SET i.vat_number=cu.vat_number 
WHERE i.customer_id = cu.customer_id;

Here you go

SET vat_number=cu.vat_number /* Syntax error around here */ The error is because the var_number column name is ambiguous - MySQL does not know if this is i.vat_number or cu,vat_number.

Something like :

UPDATE invoice in
SET vat_number=(SELECT cu.vat_number FROM customer cu 
WHERE in.customer_id=cu.customer_id)
-- not tested

UPDATE iv
SET iv.vat_number=cu.vat_number
FROM invoice iv
INNER JOIN customer cu ON iv.customer_id=cu.customer_id