问题描述:PRODUCT_TOOL表的lev列如果不为空,则拼接到product_name列,拼接的时候加上‘|’。然后更新RESEARCH_PRODUCT表的product_name列与PRODUCT_TOOL表保持一致。
UPDATE PRODUCT_TOOL t SET t.PRODUCT_NAME = t.PRODUCT_NAME || '|' || (SELECT pt.LEV FROM PRODUCT_TOOL pt WHERE pt.PRODUCT_NUM = t.PRODUCT_NUM)
WHERE t.LEV IS NOT NULL AND substr(t.PRODUCT_NAME,-LENGTH(t.LEV)) <> t.LEV;
UPDATE RESEARCH_PRODUCT rp SET rp.PRODUCT_NAME = (SELECT pt.PRODUCT_NAME FROM PRODUCT_TOOL pt WHERE pt.PRODUCT_NUM = rp.PRODUCT_NUM)
WHERE EXISTS
(SELECT 1 FROM PRODUCT_TOOL pt WHERE pt.LEV IS NOT NULL AND substr(pt.PRODUCT_NAME,-LENGTH(pt.LEV)) = pt.LEV AND pt.PRODUCT_NUM = rp.PRODUCT_NUM);
下边是更新错误的sql,把lev为空的全部拼接到了product_name列,也就是lev为空的数据product_name后边拼接了个‘|’。还有批量更新错误之后还原数据的sql。
UPDATE PRODUCT_TOOL t SET t.PRODUCT_NAME = t.PRODUCT_NAME || '|' || (SELECT pt.LEV FROM PRODUCT_TOOL pt WHERE pt.PRODUCT_NUM = t.PRODUCT_NUM)
WHERE NOT EXISTS (SELECT 1 FROM PRODUCT_TOOL pt WHERE substr(pt.PRODUCT_NAME,-LENGTH(pt.LEV)) = pt.LEV AND pt.PRODUCT_NUM = t.PRODUCT_NUM);
UPDATE PRODUCT_TOOL t SET t.PRODUCT_NAME = substr(t.PRODUCT_NAME,0,LENGTH(t.PRODUCT_NAME)-1)
WHERE NOT EXISTS (SELECT 1 FROM PRODUCT_TOOL pt WHERE substr(pt.PRODUCT_NAME,-LENGTH(pt.LEV)) = pt.LEV AND pt.PRODUCT_NUM = t.PRODUCT_NUM);
表中数据查询截图以及sql,查询已经拼接过lev字段的数据,这部分数据需要排除掉:
SELECT pt.PRODUCT_NUM,pt.PRODUCT_NAME,pt.LEV,instr(pt.PRODUCT_NAME,pt.LEV),LENGTH(pt.PRODUCT_NAME)
FROM PRODUCT_TOOL pt WHERE instr(pt.PRODUCT_NAME,pt.LEV) = (LENGTH(pt.PRODUCT_NAME) - LENGTH(pt.LEV) + 1);
SELECT pt.PRODUCT_NUM,pt.PRODUCT_NAME,pt.LEV,instr(pt.PRODUCT_NAME,pt.LEV),LENGTH(pt.PRODUCT_NAME)
FROM PRODUCT_TOOL pt WHERE instr(pt.PRODUCT_NAME,pt.LEV) > 0;