update user set active_flag=( case when exists(select 1 from org where org_id=user.user_id and status in (20,30)) then 1 when exists(select 1 from org where org_id=user.user_id and status=40) then 0 end ) where exists (select 1 from org where org_id=user.user_id and status in (20,30,40))
merge into user t1 using org t2 on (t2.org_id = t1.user_id) when matched then update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0);
merge into user t1 using org t2 on (t2.org_id = t1.user_id) when matched then update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0); 最后得加where条件吧
merge into user t1 using org t2 on (t2.org_id = t1.user_id) when matched then update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0); 最后得加where条件吧 他这是merge into的写法,match条件在on 后面写了
merge into user t1 using org t2 on (t2.org_id = t1.user_id) when matched then update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0); 最后得加where条件吧 他这是merge into的写法,match条件在on 后面写了那应该再加一个when not matched then [不做处理] 吧
merge into user t1 using org t2 on (t2.org_id = t1.user_id) when matched then update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0);这是update和insert合并的吧, 后面不写就不做insert处理了吗? 如果不写的话报错耶 ora--00905缺少关键字
case
when exists(select 1 from org where org_id=user.user_id and status in (20,30)) then 1
when exists(select 1 from org where org_id=user.user_id and status=40) then 0
end
)
where exists (select 1 from org where org_id=user.user_id and status in (20,30,40))
merge into user t1
using org t2
on (t2.org_id = t1.user_id)
when matched then
update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0);
merge into user t1
using org t2
on (t2.org_id = t1.user_id)
when matched then
update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0); 最后得加where条件吧
merge into user t1
using org t2
on (t2.org_id = t1.user_id)
when matched then
update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0); 最后得加where条件吧
他这是merge into的写法,match条件在on 后面写了
merge into user t1
using org t2
on (t2.org_id = t1.user_id)
when matched then
update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0); 最后得加where条件吧
他这是merge into的写法,match条件在on 后面写了那应该再加一个when not matched then [不做处理] 吧
如果不写的话报错耶 ora--00905缺少关键字
merge into user t1
using org t2
on (t2.org_id = t1.user_id)
when matched then
update set t1.active_flag = decode(t2.status, 20, 1, 30, 1, 0);这是update和insert合并的吧, 后面不写就不做insert处理了吗? 如果不写的话报错耶 ora--00905缺少关键字
百度下merge into的用法,好多讲解的