我想实现 的效果就是更新的时候判断是否有更新所必备的条件,如果没有的话则执行插入语句
逻辑就像下边的这段sql语句(注:下面的这段sql语句在else 部分是有错的)请高手指点一下 !加急件
UPDATE A cc
SET CC.role_id = '111'
WHERE cc.user_id = (CASE WHEN (SELECT cc1.role_id
FROM A cc1
WHERE cc1.user_id ='abc')
IS NOT NULL
THEN 'abc'
ELSE (INSERT INTO Acc1 VALUES ('111','abc')) NULL
END ) ;
逻辑就像下边的这段sql语句(注:下面的这段sql语句在else 部分是有错的)请高手指点一下 !加急件
UPDATE A cc
SET CC.role_id = '111'
WHERE cc.user_id = (CASE WHEN (SELECT cc1.role_id
FROM A cc1
WHERE cc1.user_id ='abc')
IS NOT NULL
THEN 'abc'
ELSE (INSERT INTO Acc1 VALUES ('111','abc')) NULL
END ) ;
--如果更新,新增是同一个表,用merge into就可以了,如果不是用sql语句吧,例:
declare
v_count int:=0;
begin
select count(*) into v_count from A where user_id='abc';
if v_count<>0 then
update A set role_id = '111' where user_id='abc';
else
insert into Acc1 values('111','abc');
end if;
commit;
end;
/
--如果简单点这样写,与上面的功能类似,少了个临时变量而已
begin
update A set role_id = '111' where user_id='abc';
if sql%rowcount=0 then
insert into Acc1 values('111','abc');
end if;
commit;
end;
/
SET CC.role_id = '111'
WHERE cc.user_id = (CASE WHEN (SELECT cc1.role_id
FROM A cc1
WHERE cc1.user_id ='abc')
IS NOT NULL
THEN 'abc'
ELSE (INSERT INTO A cc1 VALUES ('111','abc')) NULL
END ) ;
而且我不想用代码块儿实现,只想用普通的sql语句
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,
p.category = np.category
WHERE p.category = 'DVD'
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
前提是一张表