select a.prmatc,a.prkcoo,a.prdoco,a.prdcto,a.prlnid,a.pritm,a.prlitm,a.prjeln,a.prvani,a.pruopn,a.prurec, a.prqtys,a.praopn,a.prarec,a.pruser,b.pomcde,b.pouser,b.pooprt,b.podoc,b.podct,b.poupmj from f43121 a inner join f43099 b on a.prnlin=b.ponlin and a.prlnid=b.polnid and a.prdoco=b.podoco and a.prkcoo=b.pokcoo and a.prdcto=b.podcto where b.poupmj>=111150 and a.prmatc='1' and a.prjeln=0 and a.prvani=' ' and b.pooprt='STK'这个select语句好使。
--查出需要更新表的rowid CREATE TABLE update_midle_table AS SELECT A.ROWID AS ROW_ID --如果需要其他字段酌情添加 FROM F43121 A INNER JOIN F43099 B ON A.PRNLIN = B.PONLIN AND A.PRLNID = B.POLNID AND A.PRDOCO = B.PODOCO AND A.PRKCOO = B.POKCOO AND A.PRDCTO = B.PODCTO WHERE B.POUPMJ >= 111150 AND A.PRMATC = '1' AND A.PRJELN = 0 AND A.PRVANI = ' ' AND B.POOPRT = 'STK';--再update UPDATE F43121 A SET A.XX =''... WHERE ROWID IN(SELECT ROW_ID FROM update_midle_table);
UPDATE F43121 A SET A.XX =''... WHERE ROWID IN (SELECT ROW_ID FROM (SELECT A.ROWID AS ROW_ID FROM F43121 A INNER JOIN F43099 B ON A.PRNLIN = B.PONLIN AND A.PRLNID = B.POLNID AND A.PRDOCO = B.PODOCO AND A.PRKCOO = B.POKCOO AND A.PRDCTO = B.PODCTO WHERE B.POUPMJ >= 111150 AND A.PRMATC = '1' AND A.PRJELN = 0 AND A.PRVANI = ' ' AND B.POOPRT = 'STK' ) ); 不使用中间表,这样也可以的吧?谢谢benchi
a.prqtys,a.praopn,a.prarec,a.pruser,b.pomcde,b.pouser,b.pooprt,b.podoc,b.podct,b.poupmj
from f43121 a inner join f43099 b
on a.prnlin=b.ponlin and a.prlnid=b.polnid and a.prdoco=b.podoco and a.prkcoo=b.pokcoo and a.prdcto=b.podcto
where b.poupmj>=111150
and a.prmatc='1' and a.prjeln=0
and a.prvani=' ' and b.pooprt='STK'这个select语句好使。
--查出需要更新表的rowid
CREATE TABLE update_midle_table AS
SELECT A.ROWID AS ROW_ID --如果需要其他字段酌情添加
FROM F43121 A
INNER JOIN F43099 B ON A.PRNLIN = B.PONLIN
AND A.PRLNID = B.POLNID
AND A.PRDOCO = B.PODOCO
AND A.PRKCOO = B.POKCOO
AND A.PRDCTO = B.PODCTO
WHERE B.POUPMJ >= 111150
AND A.PRMATC = '1'
AND A.PRJELN = 0
AND A.PRVANI = ' '
AND B.POOPRT = 'STK';--再update
UPDATE F43121 A
SET A.XX =''...
WHERE ROWID IN(SELECT ROW_ID FROM update_midle_table);
UPDATE F43121 A
SET A.XX =''...
WHERE ROWID IN
(SELECT ROW_ID FROM (SELECT A.ROWID AS ROW_ID
FROM F43121 A
INNER JOIN F43099 B ON A.PRNLIN = B.PONLIN
AND A.PRLNID = B.POLNID
AND A.PRDOCO = B.PODOCO
AND A.PRKCOO = B.POKCOO
AND A.PRDCTO = B.PODCTO
WHERE B.POUPMJ >= 111150
AND A.PRMATC = '1'
AND A.PRJELN = 0
AND A.PRVANI = ' '
AND B.POOPRT = 'STK'
)
);
不使用中间表,这样也可以的吧?谢谢benchi