我是个穷小子,没分给啊
update trd_preodrlist_P001 a set rqid=(select num from (select rqno,trdt,sqno,odtp,
rownum as num from trd_preodrlist_P001 where odtp in('0','3') and stcd ='A') B where a.odtp in('0','3')
and a.RQNO=b.RQNO and a.trdt=b.trdt and a.sqno=b.sqno and a.odtp=b.odtp and a.stcd ='A')
update trd_preodrlist_P001 a set rqid=(select num from (select rqno,trdt,sqno,odtp,
rownum as num from trd_preodrlist_P001 where odtp in('0','3') and stcd ='A') B where a.odtp in('0','3')
and a.RQNO=b.RQNO and a.trdt=b.trdt and a.sqno=b.sqno and a.odtp=b.odtp and a.stcd ='A')
UPDATE TRD_PREODRLIST_P001 A
SET RQID = (SELECT ROWNUM
FROM TRD_PREODRLIST_P001 B
WHERE A.RQNO = B.RQNO
AND A.TRDT = B.TRDT
AND A.SQNO = B.SQNO
AND A.ODTP = B.ODTP
AND A.STCD = B.STCD
AND A.ODTP IN ('0', '3')
AND A.STCD = 'A');
描述下我的SQL语句要干什么:将表中的每条记录的Rownum写回该表中的RQID字段。
如:RQNO TRDT RQID
0078 20080113 1
0090 20071231 2
B.NUM
FROM TRD_PREODRLIST_P001 A,
(SELECT RQNO,
TRDT,
SQNO,
ODTP,
ROWNUM AS NUM
FROM TRD_PREODRLIST_P001
WHERE (ODTP = '0' OR ODTP = '3')
AND STCD = 'A') B
WHERE (A.ODTP = '0' OR A.ODTP = '3')
AND A.RQNO = B.RQNO
AND A.TRDT = B.TRDT
AND A.SQNO = B.SQNO
AND A.ODTP = B.ODTP
AND A.STCD = 'A') J1
SET J1.RQID = J1.ROWNUM