update TESTXX set et = null where 条件怎么样写,以下面的查出来的语数据来清空et数据
下面语句执行正常。
(select a.年龄 from
(select case LENGTH(sfzh)
when 18 then
trunc((to_char(sysdate,'yyyyMMdd') -
to_char(to_date(substr(TESTXX.SFZH,7,8),'yyyyMMdd'),'yyyyMMdd'))/10000)
when 10 then
trunc((to_char(sysdate,'yyyy') -
to_char(to_date(substr(TESTXX.SFZH,1,4),'yyyy'),'yyyy')))else 999 end as 年龄
from TESTXX where et = 1)a
where a.年龄 > 7)
下面语句执行正常。
(select a.年龄 from
(select case LENGTH(sfzh)
when 18 then
trunc((to_char(sysdate,'yyyyMMdd') -
to_char(to_date(substr(TESTXX.SFZH,7,8),'yyyyMMdd'),'yyyyMMdd'))/10000)
when 10 then
trunc((to_char(sysdate,'yyyy') -
to_char(to_date(substr(TESTXX.SFZH,1,4),'yyyy'),'yyyy')))else 999 end as 年龄
from TESTXX where et = 1)a
where a.年龄 > 7)
--可以这样:
update a set et = null
from (
select
case length(sfzh) when 18
then trunc((to_char(sysdate,'yyyyMMdd') - to_char(to_date(substr(TESTXX.SFZH,7,8),'yyyyMMdd'),'yyyyMMdd'))/10000)
when 10 then trunc((to_char(sysdate,'yyyy') - to_char(to_date(substr(TESTXX.SFZH,1,4),'yyyy'),'yyyy')))
else 999 end as 年龄
from TESTXX where et = 1
)a
where a.年龄 >7
UPDATE TESTXX
SET et = NULL
WHERE 主键Id IN ( SELECT 主键Id
FROM ( SELECT CASE LENGTH(sfzh)
WHEN 18 THEN
trunc(
(to_char(sysdate, 'yyyyMMdd')
- to_char(to_date(substr(TESTXX.SFZH, 7, 8), 'yyyyMMdd'), 'yyyyMMdd'))
/ 10000)
WHEN 10 THEN
trunc((to_char(sysdate, 'yyyy')
- to_char(to_date(substr(TESTXX.SFZH, 1, 4), 'yyyy'), 'yyyy')))
ELSE 999 END AS 年龄,
主键Id
FROM TESTXX
WHERE et = 1) a
WHERE a.年龄 > 7 );
with data as( select .... )
update data set ....