mysql子查询的问题
伊恩 2016-01-02
UPDATE
xxx_zy_supplier_item_id id
SET `status` = 1
WHERE
orderID IN (
SELECT
line.salesOrderID
FROM
xxx_zy_carrier_note note
LEFT JOIN
xxx_zy_carrier_note_line line ON note.mainID =
line.carrierNoteID
WHERE
note.mainID = 'PU20160102124222960'
)
这个sql更新了20多秒
优化后
sql语句修改成:
UPDATE
kplus_zy_supplier_item_id id,
(SELECT
line.salesOrderID as orderID
FROM
kplus_zy_carrier_note note
LEFT JOIN
kplus_zy_carrier_note_line line ON note.mainID =
line.carrierNoteID
WHERE
note.mainID = 'PU20160102124222960'
) x
SET id.status = 1
WHERE
id.orderID=x.orderID
