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?
我想用invoice.vat_number中的当前值填充invoice.vat_number中的NULL值。是否可以使用单个SQL语句执行此操作?
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;
4 个解决方案
#1
4
Using MySQL, ANSI-92 JOIN syntax:
使用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
Using MySQL, ANSI-89 JOIN syntax:
使用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
For more info, see the MySQL UPDATE documentation. This is MySQL specific UPDATE statement syntax, not likely to be supported on other databases.
有关更多信息,请参阅MySQL UPDATE文档。这是MySQL特定的UPDATE语句语法,不太可能在其他数据库上受支持。
#2
4
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.
SET vat_number = cu.vat_number / *这里的语法错误* /错误是因为var_number列名不明确 - MySQL不知道这是i.vat_number还是cu,vat_number。
#3
3
Something like :
就像是 :
UPDATE invoice in
SET vat_number=(SELECT cu.vat_number FROM customer cu
WHERE in.customer_id=cu.customer_id)
-- not tested
#4
0
UPDATE iv
SET iv.vat_number=cu.vat_number
FROM invoice iv
INNER JOIN customer cu ON iv.customer_id=cu.customer_id
#1
4
Using MySQL, ANSI-92 JOIN syntax:
使用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
Using MySQL, ANSI-89 JOIN syntax:
使用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
For more info, see the MySQL UPDATE documentation. This is MySQL specific UPDATE statement syntax, not likely to be supported on other databases.
有关更多信息,请参阅MySQL UPDATE文档。这是MySQL特定的UPDATE语句语法,不太可能在其他数据库上受支持。
#2
4
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.
SET vat_number = cu.vat_number / *这里的语法错误* /错误是因为var_number列名不明确 - MySQL不知道这是i.vat_number还是cu,vat_number。
#3
3
Something like :
就像是 :
UPDATE invoice in
SET vat_number=(SELECT cu.vat_number FROM customer cu
WHERE in.customer_id=cu.customer_id)
-- not tested
#4
0
UPDATE iv
SET iv.vat_number=cu.vat_number
FROM invoice iv
INNER JOIN customer cu ON iv.customer_id=cu.customer_id