当前位置：文江博客话题详情

忽略违反重复键索引的行的插入

发布于 2024-08-20 05:43:14 字数 436 浏览 6 评论 0 原文

我执行插入操作如下：

INSERT INTO foo (a,b,c)
   SELECT x,y,z
   FROM fubar
   WHERE ...

但是，如果插入的某些行违反了 foo 上的重复键索引，我希望数据库忽略这些行，而不插入它们并继续插入其他行。

有问题的数据库是 Informix 11.5。目前发生的只是数据库抛出异常。如果我尝试使用以下方法处理异常：

ON EXCEPTION IN (-239)
END EXCEPTION WITH RESUME;

...它没有帮助，因为捕获异常后，整个插入都会被跳过。

我不认为 informix 支持 INSERT IGNORE 或 INSERT ... ON DUPLICATE KEY...，但如果我错了，请随时纠正我。

原文

I perform an insert as follows:

INSERT INTO foo (a,b,c)
   SELECT x,y,z
   FROM fubar
   WHERE ...

However, if some of the rows that are being inserted violate the duplicate key index on foo, I want the database to ignore those rows, and not insert them and continue inserting the other rows.

The DB in question is Informix 11.5. Currently all that happens is that the DB is throwing an exception. If I try to handle the exception with:

ON EXCEPTION IN (-239)
END EXCEPTION WITH RESUME;

... it does not help because after the exception is caught, the entire insert is skipped.

I don't think informix supports INSERT IGNORE, or INSERT ... ON DUPLICATE KEY..., but feel free to correct me if I am wrong.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笑，眼淚并存 2024-08-27 05:43:14

使用 IF 语句和 EXISTS 函数检查是否存在记录。或者您可以在 WHERE 子句中包含 EXISTS 函数，如下所示

INSERT INTO foo (a,b,c) 
SELECT x,y,z 
FROM fubar 
WHERE (NOT EXISTS(SELECT a FROM foo WHERE ...))

Use IF statement and EXISTS function to check for existed records. Or you can probably include that EXISTS function in the WHERE clause like below

INSERT INTO foo (a,b,c) 
SELECT x,y,z 
FROM fubar 
WHERE (NOT EXISTS(SELECT a FROM foo WHERE ...))

回复收藏 0 原文

廻憶裏菂餘溫 2024-08-27 05:43:14

根据您是否想要了解所有错误（通常是数据加载操作的结果），请考虑使用违规表。

START VIOLATIONS TABLE FOR foo;

这将创建一对表 foo_vio 和 foo_dia 来包含有关违反表完整性约束的行的信息。

当您受够了时，您可以使用：

STOP VIOLATIONS TABLE FOR foo;

您可以在闲暇时清理诊断表。命令上有花哨的东西来控制使用哪个表等。（我也许应该注意，这假设您使用的是 IDS（IBM Informix Dynamic Server），而不是 Informix SE 或 Informix OnLine。）

违规表是重型选项 - 适用于负载等。它们通常不用于保护普通 SQL。为此，受保护的 INSERT（使用 SELECT 和 WHERE NOT EXISTS）相当有效 - 它要求数据已经在表中，但临时表很容易创建。

Depending on whether you want to know all about all the errors (typically as a result of a data loading operation), consider using violations tables.

START VIOLATIONS TABLE FOR foo;

This will create a pair of tables foo_vio and foo_dia to contain information about rows that violate the integrity constraints on the table.

When you've had enough, you use:

STOP VIOLATIONS TABLE FOR foo;

You can clean up the diagnostic tables at your leisure. There are bells and whistles on the command to control which table is used, etc. (I should perhaps note that this assumes you are using IDS (IBM Informix Dynamic Server) and not, say, Informix SE or Informix OnLine.)

Violations tables are a heavy-duty option - suitable for loads and the like. They are not ordinarily used to protect run-of-the-mill SQL. For that, the protected INSERT (with SELECT and WHERE NOT EXISTS) is fairly effective - it requires the data to be in a table already, but temp tables are easy to create.

回复收藏 0 原文

掩耳倾听 2024-08-27 05:43:14

还有其他几个选项需要考虑。

从版本 11.50 开始，Informix 支持 MERGE 语句。这可用于从 fubar 中插入行（其中 foo 中不存在相应行），并使用 fubar 中的值更新 foo 中的行（其中 foo 中已存在相应行）（重复键问题）。

另一种看待它的方式是：

SELECT fubar.*
   FROM fubar JOIN foo ON fubar.pk = foo.pk
   INTO TEMP duplicate_entries;

DELETE FROM fubar WHERE pk IN (SELECT pk FROM duplicate_entries);

INSERT INTO foo SELECT * FROM fubar;

...processs duplicate_entries

DROP TABLE duplicate_entries

在尝试插入数据之前，这会清除源表 (fubar) 中的重复条目（假设只有主键重复）。重复项表包含 fubar 中具有重复键的行 - 需要以某种形状或形式进行特殊处理的行。或者您可以简单地删除并忽略这些行，但根据我的经验，这很少是一个好主意。

There are a couple of other options to consider.

From version 11.50 onwards, Informix supports the MERGE statement. This could be used to insert rows from fubar where the corresponding row in foo does not exist, and to update the rows in foo with the values from fubar where the corresponding row already exists in foo (the duplicate key problem).

Another way of looking at it is:

SELECT fubar.*
   FROM fubar JOIN foo ON fubar.pk = foo.pk
   INTO TEMP duplicate_entries;

DELETE FROM fubar WHERE pk IN (SELECT pk FROM duplicate_entries);

INSERT INTO foo SELECT * FROM fubar;

...processs duplicate_entries

DROP TABLE duplicate_entries

This cleans the source table (fubar) of the duplicate entries (assuming it is only the primary key that is duplicated) before trying to insert the data. The duplicate_entries table contains the rows in fubar with the duplicate keys - the ones that need special processing in some shape or form. Or you can simply delete and ignore those rows, though in my experience, that is seldom a good idea.

回复收藏 0 原文

心凉 2024-08-27 05:43:14

也许是你的朋友在这方面分组。防止输入重复的行。在您的选择中使用分组依据。这将迫使重复项进入唯一的行。我唯一要做的就是测试是否存在任何性能问题。另外，请确保包含希望在分组中唯一的所有行，或者可以排除不重复的行。

INSERT INTO FOO(Name, Address, Age, Gadget, Price)
select Name, Age, Gadget, Price
from foobar
group by Name, Age, Gadget, Price

其中Name、Age、Gadget、Price构成主键索引（或唯一键索引）。
另一种可能性是将重复的行写入没有索引的错误表，然后在将它们插入新表之前解决重复项。只需要添加一个having count(*) >上述1条。

Group by maybe your friend in this. To prevent duplicate rows from being entered. Use group by in your select. This will force the duplicates into a unique row. The only thing I would do is test to see if there any performance issues. Also, make sure you include all of the rows you want to be unique in the group by or you could exclude rows that are not duplicates.

INSERT INTO FOO(Name, Address, Age, Gadget, Price)
select Name, Age, Gadget, Price
from foobar
group by Name, Age, Gadget, Price

Where Name, Age, Gadget, Price form the primary key index (or unique key index).
The other possibility is to write the duplicated rows to an error table without the index and then resolve the duplicates before inserting them into the new table. Just need to add a having count(*) > 1 clause to the above.

回复收藏 0 原文