两个不同的用户,但是表结构相同,已经建设好同义词,现在进行表结构同步,但是同步的表 MainRelation 中又有些字段为空,所以想在 matched then update时做个判断,若MainRelation表有字段为空,则不同步。下面insert中的表结构就是两个表的所有字段结构,现在卡在执行时提示 set if这里,提示缺少 =号,麻烦大神指导,感谢感谢。 merge into relation@TB b
using MainRelation c
on (b.ejycode = c.ejycode)
when matched then
update
set if (c.lecode <>'' and c.lecode is not null) then b.lecode = c.lecode,
if (c.lename <> '' and c.lename is not null) then b.lename := c.lename,
if (c.created_date <> '' and c.created_date is not null) then b.created_date := c.created_date,
if (c.update_date <> '' and c.update_date is not null) then b.updated_date := c.update_date,
if (c.flag <> '' and c.flag is not null) then b.flag = c.flag
when not matched then
insert
values
(c.ejycode,
c.lecode,
c.lename,
c.created_date,
c.update_date,
c.flag);
using MainRelation c
on (b.ejycode = c.ejycode)
when matched then
update
set if (c.lecode <>'' and c.lecode is not null) then b.lecode = c.lecode,
if (c.lename <> '' and c.lename is not null) then b.lename := c.lename,
if (c.created_date <> '' and c.created_date is not null) then b.created_date := c.created_date,
if (c.update_date <> '' and c.update_date is not null) then b.updated_date := c.update_date,
if (c.flag <> '' and c.flag is not null) then b.flag = c.flag
when not matched then
insert
values
(c.ejycode,
c.lecode,
c.lename,
c.created_date,
c.update_date,
c.flag);
set if (c.lecode <>'' and c.lecode is not null) then b.lecode = c.lecode,
-- 改成这个试试,条件成立,更新为新值,不成立,则更新来原来的值(间接达到不更新的目的—)
set b.lecode = case when c.lecode <>'' and c.lecode is not null then c.lecode else b.lecode end
set if (c.lecode <>'' and c.lecode is not null) then b.lecode = c.lecode 改写成update
set n b.lecode =decode(c.lecode,'',b.lecode ,c.lecode)
修正一下,多一个Nupdate
set if (c.lecode <>'' and c.lecode is not null) then b.lecode = c.lecode 改写成update
set b.lecode =decode(c.lecode,'',b.lecode ,c.lecode