UPDATE FX_ACCOUNTDETAILS A SET A.DETAILID=( SELECT NVL(B.ID,NVL(C.ID,A.DETAILID)) FROM FX_DELIVERYCHECKLIST B,FX_RETURNBOOKCHECKLIST C WHERE A.DETAILID=B.OLDIDENTIFY AND C.OLDIDENTIFY=A.DETAILID)用这样的语句是否会好点? 有没有高手指点下这两种查询的区别第2部分一样的写法
弄好了,谢谢各位。分两个update: --与发货明细关联 UPDATE FX_ACCOUNTDETAILS A SET A.DETAILID=( SELECT CASE WHEN B.ID IS NULL THEN A.DETAILID ELSE B.ID END FROM FX_DELIVERYCHECKLIST B WHERE A.DETAILID=B.OLDIDENTIFY ), A.INVOICETYPE=( SELECT CASE WHEN B.ID IS NULL THEN NULL ELSE 0 END FROM FX_DELIVERYCHECKLIST B WHERE A.DETAILID=B.OLDIDENTIFY ); COMMIT; --与退货明细关联 UPDATE FX_ACCOUNTDETAILS A SET A.DETAILID=( SELECT CASE WHEN B.ID IS NULL THEN A.DETAILID ELSE B.ID END FROM FX_RETURNBOOKCHECKLIST B WHERE A.DETAILID=B.OLDIDENTIFY ), A.INVOICETYPE=( SELECT CASE WHEN B.ID IS NULL THEN NULL ELSE 1 END FROM FX_RETURNBOOKCHECKLIST B WHERE A.DETAILID=B.OLDIDENTIFY ); COMMIT;
SELECT NVL(B.ID,NVL(C.ID,A.DETAILID))
FROM FX_DELIVERYCHECKLIST B,FX_RETURNBOOKCHECKLIST C
WHERE A.DETAILID=B.OLDIDENTIFY AND C.OLDIDENTIFY=A.DETAILID)用这样的语句是否会好点?
有没有高手指点下这两种查询的区别第2部分一样的写法
--与发货明细关联
UPDATE FX_ACCOUNTDETAILS A SET A.DETAILID=(
SELECT
CASE WHEN B.ID IS NULL THEN A.DETAILID
ELSE B.ID
END
FROM
FX_DELIVERYCHECKLIST B
WHERE A.DETAILID=B.OLDIDENTIFY
),
A.INVOICETYPE=(
SELECT
CASE WHEN B.ID IS NULL THEN NULL
ELSE 0
END
FROM
FX_DELIVERYCHECKLIST B
WHERE A.DETAILID=B.OLDIDENTIFY
);
COMMIT;
--与退货明细关联
UPDATE FX_ACCOUNTDETAILS A SET A.DETAILID=(
SELECT
CASE WHEN B.ID IS NULL THEN A.DETAILID
ELSE B.ID
END
FROM
FX_RETURNBOOKCHECKLIST B
WHERE A.DETAILID=B.OLDIDENTIFY
),
A.INVOICETYPE=(
SELECT
CASE WHEN B.ID IS NULL THEN NULL
ELSE 1
END
FROM
FX_RETURNBOOKCHECKLIST B
WHERE A.DETAILID=B.OLDIDENTIFY
);
COMMIT;