批量插入/更新/删除 [英] Bulk Insert / Update / Delete

问题描述

我希望能够生成SQL语句，这些SQL语句将逐行查看

数据列表，询问数据库是否存在于
$ b $中b选择表 - 如果它存在，那么colums必须是UPDATED，否则，它们必须是b
$ b。


逻辑然后，我想SELECT * FROM< TABLE>

WHERE ....<此处输入的值>，然后IF FOUND

UPDATE< TABLE> SET ....<此处输入的值> ELSE

INSERT INTO< TABLE> VALUES<此处输入的值>

END IF;


IF语句被解析器拒绝。所以看来

PostgreSQL在这种类型的查询中不支持IF，或者可能不支持

all。


有没有人对如何实现这一点有任何建议？

此消息是特权和保密的，仅供收件人使用。如果您不是预定的收件人，您不得以任何方式使用或发布本文的内容，不得以任何方式披露，复制或

，这些内容受版权保护。如果您错误地收到此信息，请销毁原件。消息

并通过 po********@cks.co与我们联系.za 。此消息中表达的任何观点

是个别发件人的观点，除非发件人明确指出他们是Computerkit Retail Systems，其子公司或其他人的观点。 />
员工。请注意，收件人必须扫描此电子邮件和附件是否有病毒。对于此消息和/或附件直接或间接导致的任何损失，

责任，损害或费用，我们不承担任何责任。

此消息和/或附件。


---------------------------（广播结束）--------- ------------------

提示5：您查看了我们广泛的常见问题解答吗？

http://www.postgresql.org/docs/faqs/FAQ.html

I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.

Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;

The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.

Does anyone have any suggestions as to how I can achieve this ?
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at po********@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

推荐答案

菲利普，


Pg比大多数人更符合ansi（GoodThing（TM） ））。您可以使用''when''

有条件但不能做你需要的。如果我理解你的意思，那么
应该能够使用两个单独的查询和

（NOT）EXISTS或（NOT）IN子句来实现相同的结果。如果没有看到pl / pgsql和其他postgresql过程语言中的精美文档

，它们允许你使用

循环和条件语句，如''if''。


Rgds，

J


2003年8月20日星期三下午12:21， Philip Boonzaaier写道：

Hi Philip,

Pg is more ansi compliant than most (GoodThing (TM)). You can use the ''when''
conditional but not to do what you need. If I understand you correclty you
should be able to acheive the same result using two seperate queries and the
(NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine docs
on pl/pgsql and other postgresql procedural languages which allow you to use
loops and conditional statements like ''if''.

Rgds,

J

On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:

我希望能够生成SQL语句，这些语句将逐行查询数据列表，查询数据库是否存在于
选择表 - 如果它存在，那么colums必须是UPDATED，否则，它们必须被INSERTED。

然后，我想SELECT * FROM< TABLE>
在哪里....<这里输入的值>，然后如果发现
UPDATE< TABLE> SET ....<此处输入的值> ELSE
INSERT INTO< TABLE> VALUES<此处输入的值>
END IF;

IF语句被解析器拒绝。所以看起来PostgreSQL在这种类型的查询中不支持IF，或者可能不支持


有没有人对我如何实现这一点有任何建议这个？

此消息是特权和机密的，仅供收件人使用
。如果您不是预定的收件人，您不得以任何方式披露，复制或以任何方式使用或发布本文的内容，这些内容受版权保护。如果您错误地收到此内容，请销毁原件。
消息并通过 po ******** @ cks.co.za 。本消息中表达的任何观点均为个人发件人的观点，除非发件人明确声明他们是Computerkit Retail Systems，其子公司或同事的观点。请注意，收件人必须扫描此
电子邮件和附件是否有病毒。对于因此传输此消息和/或附件而间接或间接导致的任何损失，责任，损害或费用，我们不承担任何责任。

--- ------------------------（播出结束）--------------------- ------
提示5：您查看了我们广泛的常见问题解答吗？

http://www.postgresql.org/docs/faqs/FAQ.html

I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.

Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;

The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.

Does anyone have any suggestions as to how I can achieve this ?
This message is privileged and confidential and intended for the addressee
only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to
copyright.If you have received this in error, please destroy the original
message and contact us at po********@cks.co.za. Any views expressed in this
message are those of the individual sender, except where the sender
specifically states them to be the view of Computerkit Retail Systems, its
subsidiaries or associates. Please note that the recipient must scan this
e-mail and attachments for viruses. We accept no liability of whatever
nature for any loss, liability,damage or expense resulting directly or
indirectly from this transmission of this message and/or attachments.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

----- ----------------------（播出结束）----------------------- ----

提示4：不要杀死-9''邮政局长

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

" Philip Boonzaaier" < pH值** @ cks.co.za>写道：

"Philip Boonzaaier" <ph**@cks.co.za> writes:

我希望能够生成SQL语句，这些语句将逐行查询数据列表，查询数据库是否存在于数据库中
选中的表 - 如果它存在，那么colums必须是UPDATED，否则，它们必须被INSERTED。

然后逻辑然后，我想SELECT * FROM<表>
WHERE ....<此处输入的值>，然后IF FOUND
UPDATE< TABLE> SET ....<此处输入的值> ELSE
INSERT INTO< TABLE> VALUES<此处输入的值>
END IF;

IF语句被解析器拒绝。所以看起来PostgreSQL在这种类型的查询中不支持IF，或者可能不支持所有。


不。我不知道有哪个SQL数据库可以做到，虽然我当然没有看到他们所有人都没有看到...

有没有人有任何建议我怎么能做到这一点？


应用程序代码循环遍历第一个查询的结果，

并根据需要发出UPDATE / INSERT语句？或者你可以把它作为PL / pgSQL函数来实现它可能会快一点。

这条消息是特权和机密的，仅供收件人使用。如果您不是预期收件人，则不得以任何方式披露，复制或以任何方式使用或发布受其版权保护的内容。如果您错误地收到此内容，请销毁原始邮件并通过 po********@cks.co.za 与我们联系。此消息中表达的任何观点都是单个发件人的观点，除非发件人特别声明他们是Computerkit Retail Systems，其子公司或
员工的观点。请注意，收件人必须扫描此电子邮件和附件是否有病毒。对于此消息和/或附件的传输直接或间接导致的任何损失，责任，损害或费用，我们不承担任何性质的责任。

I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.

Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values entered here>
END IF;

The IF statement gets rejected by the parser. So it would appear that
PostgreSQL does not support an IF in this type of query, or maybe not at
all.
Nope. I don''t know of an SQL database that does, though I certainly
haven''t seen all of them...
Does anyone have any suggestions as to how I can achieve this ?
Application code that loops through the results of the first query,
and issues UPDATE/INSERT statements as needed? Or you could do it as
a PL/pgSQL function which might be a little faster.
This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at po********@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.

我有公司在邮件列表上张贴这样的垃圾...


-Doug


---- -----------------------（播出结束）---------------------- -----

提示4：不要杀死-9''邮政局长

I have companies that force crap like this on mailing list postings...

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

Doug McNaught <做** @ mcnaught.org>写道：

Doug McNaught <do**@mcnaught.org> writes:

" Philip Boonzaaier" < pH值** @ cks.co.za>写道：

"Philip Boonzaaier" <ph**@cks.co.za> writes:

此消息具有特权和保密性，仅供收件人使用。如果您不是预期收件人，则不得以任何方式披露，复制或以任何方式使用或发布受其版权保护的内容。如果您错误地收到此内容，请销毁原始邮件并通过 po********@cks.co.za 与我们联系。此消息中表达的任何观点都是单个发件人的观点，除非发件人特别声明他们是Computerkit Retail Systems，其子公司或
员工的观点。请注意，收件人必须扫描此电子邮件和附件是否有病毒。对于此消息和/或附件的传输直接或间接导致的任何损失，责任，损害或费用，我们不承担任何性质的责任。

This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
and contact us at po********@cks.co.za. Any views expressed in this message
are those of the individual sender, except where the sender specifically
states them to be the view of Computerkit Retail Systems, its subsidiaries or
associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
liability,damage or expense resulting directly or indirectly from this transmission
of this message and/or attachments.

我有公司在邮件列表上张贴这样的垃圾...

I have companies that force crap like this on mailing list postings...

^^^^讨厌


Arrghh。


-Doug


--------------------------- （播出结束）---------------------------

提示7：别忘了增加你的免费空间地图设置

^^^^ hate

Arrghh.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

这篇关于批量插入/更新/删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！