mysql - 更新从同一查询中的 SELECT 检索的结果

Question

我需要在后续 SELECT 查询的结果中将“payments”表的“payment_status”列更新为“1”，但在单个查询中。请帮助我。

SELECT * FROM payments p
INNER JOIN invoice i
ON i.invoice_number = p.invoiceGRN_id
WHERE i.invoice_status = '2' AND i.payment_type = 'Credit'

Answer 1

您可以使用 INNER JOIN 执行 UPDATE

UPDATE payments p
INNER JOIN invoice i
ON i.invoice_number = p.invoiceGRN_id
SET p.payment_status = 1
WHERE i.invoice_status = '2' AND i.payment_type = 'Credit'

或者你可以这样做(效率较低，但更直接地回答你的问题):

UPDATE payments 
SET payment_status = 1 
WHERE id IN (
    SELECT p.id FROM payments p
    INNER JOIN invoice i
    ON i.invoice_number = p.invoiceGRN_id
    WHERE i.invoice_status = '2' AND i.payment_type = 'Credit'
)