UPDATE TLOTMM A SET A.FPRICE = (select FPRICE from TBTRHI WHERE FTRTYP IN ('DR','AR') AND FTDATE >= TO_DATE('20060501','YYYYMMDD') AND FCURCD = 'HKD' and A.FLTN = FLTNUM) WHERE FLTN IN ( SELECT FLTNUM FROM TBTRHI B WHERE FTRTYP IN ('DR','AR') AND FTDATE >= TO_DATE('20060501','YYYYMMDD') AND FCURCD = 'HKD');
UPDATE TLOTMM A SET A.FPRICE = (select FPRICE from TBTRHI WHERE FTRTYP IN ('DR','AR') AND FTDATE >= TO_DATE('20060501','YYYYMMDD') AND FCURCD = 'HKD' and A.FLTN = FLTNUM) WHERE exists( SELECT 1 FROM TBTRHI B WHERE FTRTYP IN ('DR','AR') AND FTDATE >= TO_DATE('20060501','YYYYMMDD') AND FCURCD = 'HKD' and A.FLTN = B.FLTNUM); 这样效率可能会高点。
UPDATE TLOTMM A SET A.FPRICE = (select c.FPRICE from TBTRHI c WHERE A.FLTN = c.FLTNUM and c.FTRTYP IN ('DR','AR') AND c.FCURCD = 'HKD' AND c.FTDATE >= TO_DATE('20060501','YYYYMMDD')) WHERE exists(SELECT 1 FROM TBTRHI B WHERE A.FLTN = B.FLTNUM and FTRTYP IN ('DR','AR') AND FCURCD = 'HKD' AND FTDATE >= TO_DATE('20060501','YYYYMMDD'));
UPDATE TLOTMM A SET A.FPRICE = (select B.FPRICE from TBTRHI B where b.FTRTYP IN ('DR','AR') AND b.FTDATE >= TO_DATE('20060501','YYYYMMDD') AND b.FCURCD = 'HKD' and a.FLTN = b.FLTNUM ) where exists ( select 1 from TBTRHI C where c.FTRTYP IN ('DR','AR') AND c.FTDATE >= TO_DATE('20060501','YYYYMMDD') AND c.FCURCD = 'HKD' and a.FLTN = c.FLTNUM )B.FPRICE WHERE FLTN IN ( SELECT FLTNUM FROM TBTRHI B WHERE FTRTYP IN ('DR','AR') AND FTDATE >= TO_DATE('20060501','YYYYMMDD') AND FCURCD = 'HKD') AND A.FLTN = B.FLTNUM
UPDATE TLOTMM A SET A.FPRICE = B.FPRICE WHERE exists ( SELECT 1 FROM TBTRHI B WHERE B.FTRTYP IN ('DR','AR') AND B.FTDATE >= TO_DATE('20060501','YYYYMMDD') AND B.FCURCD = 'HKD' AND A.FLTN = B.FLTNUM)
UPDATE TLOTMM A SET A.FPRICE = B.FPRICE WHERE exists ( SELECT * FROM TBTRHI B WHERE FTRTYP IN ('DR','AR') AND FTDATE >= TO_DATE('20060501','YYYYMMDD') AND FCURCD = 'HKD') AND A.FLTN = B.FLTNUM或: UPDATE TLOTMM A SET FPRICE = B.FPRICE from TLOTMM A ,TBTRHI B WHERE FLTN IN ( SELECT FLTNUM FROM TBTRHI B WHERE FTRTYP IN ('DR','AR') AND FTDATE >= TO_DATE('20060501','YYYYMMDD') AND FCURCD = 'HKD') AND A.FLTN = B.FLTNUM
oracle里update后面只能有一个表
不知道能不能帮你,试试这个看怎么样。 UPDATE TLOTMM A SET A.FPRICE = (Select B.FPRICE from TBTRHI B Where A.FLTN = B.FLTNUM) WHERE A.FTRTYP IN ('DR', 'AR') AND A.FTDATE >= TO_DATE('20060501', 'YYYYMMDD') AND A.FCURCD = 'HKD')
WHERE FTRTYP IN ('DR','AR')
AND FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND FCURCD = 'HKD' and A.FLTN = FLTNUM)
WHERE FLTN IN (
SELECT FLTNUM FROM TBTRHI B
WHERE FTRTYP IN ('DR','AR')
AND FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND FCURCD = 'HKD');
WHERE FTRTYP IN ('DR','AR')
AND FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND FCURCD = 'HKD' and A.FLTN = FLTNUM)
WHERE exists(
SELECT 1 FROM TBTRHI B
WHERE FTRTYP IN ('DR','AR')
AND FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND FCURCD = 'HKD' and A.FLTN = B.FLTNUM);
这样效率可能会高点。
WHERE A.FLTN = c.FLTNUM
and c.FTRTYP IN ('DR','AR')
AND c.FCURCD = 'HKD'
AND c.FTDATE >= TO_DATE('20060501','YYYYMMDD'))
WHERE exists(SELECT 1 FROM TBTRHI B
WHERE A.FLTN = B.FLTNUM
and FTRTYP IN ('DR','AR')
AND FCURCD = 'HKD'
AND FTDATE >= TO_DATE('20060501','YYYYMMDD'));
(select B.FPRICE from TBTRHI B where b.FTRTYP IN ('DR','AR')
AND b.FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND b.FCURCD = 'HKD'
and a.FLTN = b.FLTNUM )
where exists ( select 1 from TBTRHI C where c.FTRTYP IN ('DR','AR')
AND c.FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND c.FCURCD = 'HKD'
and a.FLTN = c.FLTNUM )B.FPRICE
WHERE FLTN IN (
SELECT FLTNUM FROM TBTRHI B
WHERE FTRTYP IN ('DR','AR')
AND FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND FCURCD = 'HKD')
AND A.FLTN = B.FLTNUM
WHERE exists (
SELECT 1 FROM TBTRHI B
WHERE B.FTRTYP IN ('DR','AR')
AND B.FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND B.FCURCD = 'HKD'
AND A.FLTN = B.FLTNUM)
WHERE exists (
SELECT * FROM TBTRHI B
WHERE FTRTYP IN ('DR','AR')
AND FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND FCURCD = 'HKD')
AND A.FLTN = B.FLTNUM或:
UPDATE TLOTMM A SET FPRICE = B.FPRICE from TLOTMM A ,TBTRHI B
WHERE FLTN IN (
SELECT FLTNUM FROM TBTRHI B
WHERE FTRTYP IN ('DR','AR')
AND FTDATE >= TO_DATE('20060501','YYYYMMDD')
AND FCURCD = 'HKD')
AND A.FLTN = B.FLTNUM
UPDATE TLOTMM A
SET A.FPRICE = (Select B.FPRICE from TBTRHI B Where A.FLTN = B.FLTNUM)
WHERE A.FTRTYP IN ('DR', 'AR')
AND A.FTDATE >= TO_DATE('20060501', 'YYYYMMDD')
AND A.FCURCD = 'HKD')