使用Case语句简单插入 [英] Simple Insert using Case statement

问题描述

我有一个有2列的表格


** CREATE TABLE test（emp_num DECIMAL（7）NOT NULL， emp_name CHAR（10）NOT


NULL）我插入了一些记录。


**现在，我想插入一个新记录（3232，''Raindeer''）基于

emp_num 3232不存在的

条件。


SELECT *，

CASE

不存在时（SELECT * from test where emp_num = 3232）

然后插入测试值（3232，' 'Raindeer''）

END

FROM test" ;;


**我收到以下错误::

SQL0104N意外的令牌*在SELECT之后被发现。

预期的代币可能包括：？


**任何人都可以帮我修改此代码。我将不胜感激，如果有一些

一个人会b / b
告诉我实现输出的不同变化。

提前谢谢。

解决方案

在DB2中查找MERGE语句。它被称为upsert。在

文献中，它是UPDATE和INSERT的组合。

我试过MERGE。但它也没有用。


MERGE INTO测试A

使用测试B

ON A.emp_num = B.emp_num

当匹配时

更新

SET A.emp_num = B.emp_num

当没有匹配时

INSERT

VALUES（3232，''success''）;


**显示DB20000I SQL命令成功完成。


**但是当我尝试选择*从测试记录（3232，''成功''）是

不存在

提前致谢

为什么不在emp_num列上创建唯一索引？这样，如果检测到重复键，

插入将失败。如果条件比这更复杂，那么你总是可以创建一个检查约束来阻止用户插入无效数据。

Hi,

I have a table with 2 columns

** CREATE TABLE test (emp_num DECIMAL(7) NOT NULL,emp_name CHAR(10) NOT

NULL) and i have inserted a number of records.

** Now, I want to insert a new record (3232,''Raindeer'') based on the
condition that the
emp_num 3232 doesnt exist.

SELECT * ,
CASE
when not exists (SELECT * from test where emp_num=3232)
then insert into test values (3232,''Raindeer'')
END
FROM test";

** I get the following error ::
SQL0104N An unexpected token "*" was found following "SELECT ".
Expected tokens may include: "?

** Can anyone help me to modify this code. I would appreciate if some
one would
show me the different variations in acheiving the output.
Thanks in advance.

解决方案

Look up the MERGE statement in DB2. It is called an "upsert" in the
literature and it is a combination of an UPDATE and INSERT.

I tried MERGE. But it is not working either.

MERGE INTO test A
USING test B
ON A.emp_num =B.emp_num
WHEN MATCHED THEN
UPDATE
SET A.emp_num =B.emp_num
WHEN NOT MATCHED THEN
INSERT
VALUES (3232,''success'');

** it showed DB20000I The SQL command completed successfully.

** But when i tried select * from test the record (3232,''success'') was
not present

Thanks in advance

Why not just create a unique index on the emp_num column? That way, the
insert would fail if a duplicate key was detect. If the condition is
more complex than that, you can always create a check constraint to
stop user from inserting invalid data.

这篇关于使用Case语句简单插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！