A表和ws_worksheet_instance_t结构相同? insert into A SELECT * FROM ws_worksheet_instance_t WHERE pick_status=3 AND worksheet_type in (99,803) 直接插入? 还是需求又理解偏了,问题描述的不是很清楚
应该是仅限被更新的记录范围吧 ELSE -- 先复制再更新,避免已有pick_status = 3的数据扩大了插入范围 INSERT INTO A SELECT worksheet_id, ... -- 所有字段都要列出来,除了pick_status和failure_reason用将要更新的值代替。 3, -- pick_status SUBSTR (vs_return_desc, 0, 100) -- failure_reason WHERE worksheet_id = vs_worksheet_id AND register_number = vs_register_no AND pick_status = 10 AND worksheet_type in (99,803); UPDATE ws_worksheet_instance_t SET pick_status = 3, failure_reason = SUBSTR (vs_return_desc, 0, 100) WHERE worksheet_id = vs_worksheet_id AND register_number = vs_register_no AND pick_status = 10; END IF;
insert into A
SELECT * FROM ws_worksheet_instance_t
WHERE pick_status=3 AND worksheet_type in (99,803)
直接插入?
还是需求又理解偏了,问题描述的不是很清楚
ELSE
-- 先复制再更新,避免已有pick_status = 3的数据扩大了插入范围
INSERT INTO A
SELECT worksheet_id, ... -- 所有字段都要列出来,除了pick_status和failure_reason用将要更新的值代替。
3, -- pick_status
SUBSTR (vs_return_desc, 0, 100) -- failure_reason
WHERE worksheet_id = vs_worksheet_id
AND register_number = vs_register_no
AND pick_status = 10
AND worksheet_type in (99,803); UPDATE ws_worksheet_instance_t
SET pick_status = 3,
failure_reason = SUBSTR (vs_return_desc, 0, 100)
WHERE worksheet_id = vs_worksheet_id
AND register_number = vs_register_no
AND pick_status = 10;
END IF;