用另一个表中的值更新表

使用mysql、ansi-92连接语法:

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连接语法:

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 invoice i, customer cu SET i.vat_number=cu.vat_number 
WHERE i.customer_id = cu.customer_id;

干得好

在此处设置vat_number=cu.vat_number/*语法错误*/ 错误是因为var_number列名称不明确-MySQL不知道这是i.vat_number还是cu,vat_number。

比如:

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