|
|
Posted by ian m via SQLMonster.com on 10/01/58 11:25
Hi,
I currently have a ms access update query that runs
perfectly well and quicly in access however I now need to add this query
to convert this qeryu to oracles equivelant sql syntax and add it to the end
of an oracle sql script.
Unfortunately Im not having much success although i seem to be able to
convert it to a working oracle sql. it takes hours to run the statement in
oracle where as in access it runs in seconds
any help is appreciated.
Ms Access sql :
UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.
STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON
(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND
(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND
(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND
(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)
SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,
PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tprice
WHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));
Oracle SQL :
update pro.tst_rv3x_rpt_wrk x
set(x.mkd_date, x.mkd_price) =
(Select a.new_active_date, a.new_tprice
from pro.sty_tprices a, pro.style_colours b
where a.sty_id=b.sty_id
and b.bf_mat_char_val = x.bf_mat_char_val
and b.season = x.season
and b.sty_num = x.sty_num
and b.sty_qual = x.sty_qual
and a.new_active_date is not null
)
Navigation:
[Reply to this message]
|