我有这些表：
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
我想用invoice.vat_number中的当前值填充invoice.vat_number中的NULL值.是否可以使用单个sql语句执行此操作？
我到目前为止触发了语法错误：
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;
最佳答案
使用MysqL,ANSI-92 JOIN语法：
UPDATE INVOICE
JOIN CUSTOMER ON CUSTOMER.customer_id = INVOICE.customer_id
SET vat_number = CUSTOMER.vat_number
WHERE INVOICE.vat_number IS NULL
使用MysqL,ANSI-89 JOIN语法：
UPDATE INVOICE,CUSTOMER
SET INVOICE.vat_number = CUSTOMER.vat_number
WHERE CUSTOMER.customer_id = INVOICE.customer_id
AND INVOICE.vat_number IS NULL
对于more info,see the MySQL UPDATE documentation.这是MysqL特定的UPDATE语句语法,不太可能在其他数据库上受支持.