CODEWITHSUNDEEP
mysqlsqlsql-update
Álvaro González
Álvaro González

Reputation: 146450

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 with 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;

Upvotes: 5

Views: 463

Answers (4)

franklins
franklins

Reputation: 3738

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

Upvotes: 0

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

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.

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 4

user180100
user180100

Reputation:

Something like :

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

Upvotes: 3

Related Questions