UPDATE C1CD_HOUSE_BANK A
INNER JOIN (
SELECT A.ROW_ID,CASE WHEN A.PARENT_ID>0 THEN B.BANK_CODE ELSE A.BANK_CODE END AS BANK_CODE,A.BANK_PREFIX PREFIX_NAMES
FROM C1CD_BANK A
LEFT JOIN C1CD_BANK B ON A.PARENT_ID=B.ROW_ID AND B.DELETED_FLAG=‘0’
WHERE A.DELETED_FLAG=‘0’ AND A.USE_TYPE IN (‘2’,‘4’)
UNION ALL
SELECT A.ROW_ID,CASE WHEN A.PARENT_ID>0 THEN C.BANK_CODE ELSE A.BANK_CODE END AS BANK_CODE,B.PREFIX_NAMES
FROM C1CD_BANK A
JOIN C1CD_BANK_EXP B ON A.ROW_ID=B.BANK_ID AND B.DELETED_FLAG=‘0’
LEFT JOIN C1CD_BANK C ON A.PARENT_ID=C.ROW_ID AND C.DELETED_FLAG=‘0’
WHERE A.DELETED_FLAG=‘0’ AND A.USE_TYPE=‘3’
) B ON (regexp_instr(A.HOUSE_BANK_NAME,REPLACE((CASE WHEN B.PREFIX_NAMES IS NULL OR LENGTH(B.PREFIX_NAMES) = 0 THEN ’ ’ ELSE B.PREFIX_NAMES END),’,’,’|’))>0 AND A.DELETED_FLAG=‘0’)
SET A.BANK_ID=B.ROW_ID
WHERE A.DELETED_FLAG=‘0’
024-04-18 13:41:40java.sql.SQLException: Timeout, query has reached the maximum query timeout: 900000000(us), maybe you can adjust the session variable ob_query_timeout or query_timeout hint, and try again.
该sql在mysql上面正常5分钟-8分钟能完成