主要用case语句,我写的如下,没测试过,不过case我以前用过
UPDATE Reg a
SET flag=(
SELECT (
CASE WHEN (b.finish IS NULL) AND (b.STOP<SYSDATE))
THEN '1'
WHEN (a.regdate BETWEEN b.STOP AND b.finish)
THEN '1'
ELSE '0'
END
)
FROM log b
WHERE a.id=b.id
)
WHERE EXISTS
(
SELECT 'X'
FROM LOG b
WHERE a.id=b.id
)
UPDATE Reg a
SET flag=(
SELECT (
CASE WHEN (b.finish IS NULL) AND (b.STOP<SYSDATE))
THEN '1'
WHEN (a.regdate BETWEEN b.STOP AND b.finish)
THEN '1'
ELSE '0'
END
)
FROM log b
WHERE a.id=b.id
)
WHERE EXISTS
(
SELECT 'X'
FROM LOG b
WHERE a.id=b.id
)
update reg set flag=decode(tab.sm,0,'0','1') where (select count(*) sm from logkey,reg where (reg.regdate between logkey.stop and logkey.finish) or (logkey.finish = NULL and to_char(logkey.stop,'yyyy-mm-dd')<to_char(sysdate,'yyyy-mm-dd'))) tab;
update reg a set flag=(select 1 from log where id=a.id and ((RegDate between stop and finish) or (stop<syadate and finish is null)))
两位是否有考虑?
3 1 2003-4-1 2003-4-30
3 2 2003-5-2 2003-5-4
where b.id=a.id and ((a.RegDate > b.stop and a.RegDate < b.finish) or
(b.stop<syadate and b.finish is null)))