我有以下查询,可以正常工作(请参阅我的其他帖子)。
SELECT sys_annot from (
SELECT SYS_ANNOT, row_number() over (order by case when prep_seq_num = 0 then 1 else 2 end) as rn
FROM BILL
WHERE BIMA_TRACKING_ID = 10
AND BSCO_CODE_ID = 'PINGPONG'
AND PARTITION_KEY = '06-DEC-17'
AND SUBPARTITION_KEY = 84
AND
(
( PREP_SEQ_NUM = 0
AND ITEM_CAT_CODE_ID=1
AND PRIMARY_IND=0
AND CHRG_ACCT_ID = 1
AND SYS_ANNOT <> 0
)
OR
( PREP_SEQ_NUM <> 0
AND ITEM_CAT_CODE_ID in ('5', '-100')
AND PRIMARY_IND=1
)
)
)
where rn=1
;
同时,我现在想更新SYS_ANNOT字段,仅在上述查询返回的单行中,即:
update BILL SET SYS_ANNOT=100
where ????
无论我尝试什么都无法正常工作,您的好心在这里有所帮助。谢谢
您应该使用主键来识别行的唯一记录。在这种情况下,您可以使用ROWID(仅与Oracle一起使用)。对于多行来说,这不是很有效,但是对于一行来说,这已经足够了:
update BILL b SET SYS_ANNOT=100
where b.rowid in (
SELECT rid from (
SELECT bill.rowid rid, SYS_ANNOT, row_number() over (order by case when prep_seq_num = 0 then 1 else 2 end) as rn
FROM BILL
WHERE BIMA_TRACKING_ID = 10
AND BSCO_CODE_ID = 'PINGPONG'
AND PARTITION_KEY = '06-DEC-17'
AND SUBPARTITION_KEY = 84
AND
(
( PREP_SEQ_NUM = 0
AND ITEM_CAT_CODE_ID=1
AND PRIMARY_IND=0
AND CHRG_ACCT_ID = 1
AND SYS_ANNOT <> 0
)
OR
( PREP_SEQ_NUM <> 0
AND ITEM_CAT_CODE_ID in ('5', '-100')
AND PRIMARY_IND=1
)
)
)
where rn=1);
您可以使用 = 而不是在中,如果您确定只返回一行。
不确定我理解这个问题,但是你不应该嵌套另一个语句吗?
我的意思是您可以在开始时添加update BILL SET SYS_ANNOT=100
where ????
,例如:
update BILL SET SYS_ANNOT=100
where SYS_ANNOT = (SELECT sys_annot from (
SELECT SYS_ANNOT, row_number() over (order by case when prep_seq_num = 0 then 1 else 2 end) as rn
FROM BILL
WHERE BIMA_TRACKING_ID = 10
AND BSCO_CODE_ID = 'PINGPONG'
AND PARTITION_KEY = '06-DEC-17'
AND SUBPARTITION_KEY = 84
AND
(
( PREP_SEQ_NUM = 0
AND ITEM_CAT_CODE_ID=1
AND PRIMARY_IND=0
AND CHRG_ACCT_ID = 1
AND SYS_ANNOT <> 0
)
OR
( PREP_SEQ_NUM <> 0
AND ITEM_CAT_CODE_ID in ('5', '-100')
AND PRIMARY_IND=1
)
)
)
where rn=1
);