Re: Bulk Insert / Update / Delete
You are going to need a procedural language function. There are several
to choose from, they all support some kind of conditional loop. There
is plenty of documentation avaiable at postgresql.org.
Philip Boonzaaier wrote:
Show quoted text
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 postmaster@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?
Import Notes
Reference msg id not found: 003a01c366c1$c5705320$e701f00a@240.1.139.196.23.149.50
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
Show quoted text
On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
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 postmaster@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?
Import Notes
Reply to msg id not found: 003a01c366c1$c5705320$e701f00a@240.1.139.196.23.149.50Reference msg id not found: 003a01c366c1$c5705320$e701f00a@240.1.139.196.23.149.50 | Resolved by subject fallback
"Philip Boonzaaier" <phil@cks.co.za> writes:
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 postmaster@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...
-Doug
Import Notes
Reply to msg id not found: PhilipBoonzaaier'smessageofWed20Aug2003042125+0200Reference msg id not found: 003a01c366c1$c5705320$e701f00a@240.1.139.196.23.149.50 | Resolved by subject fallback
Doug McNaught <doug@mcnaught.org> writes:
"Philip Boonzaaier" <phil@cks.co.za> writes:
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 postmaster@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...
^^^^ hate
Arrghh.
-Doug
Import Notes
Reply to msg id not found: DougMcNaught'smessageof19Aug2003112333-0400Reference msg id not found: 003a01c366c1$c5705320$e701f00a@240.1.139.196.23.149.50
On Tuesday 19 August 2003 20:54, Doug McNaught wrote:
Doug McNaught <doug@mcnaught.org> writes:
I have companies that force crap like this on mailing list postings...
^^^^ hate
Arrghh.
Not to troll, but another mailing list I am on, anybody posting such
messages/footers is politely excused with links to free webmail services that
offer clean text mails. Some known domains are also barred from joining
mailing lists,,
Can not afford to spam excess to 3000 subscribers most of whom pay expensive
metered dial up access. The is a justified logic behind the actions.
Shridhar
On Tue, 2003-08-19 at 22:03, Philip Boonzaaier wrote:
Hi Jason
Thanks for your prompt response.
I'm pretty new to SQL, so please excuse the following rather stupid question
:How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
using your suggestion, to simply put in two SQL statements, in the same
query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to accomplist
this in one go ?Regards
Phil
How will you which records were updated, thus able to know which need
to be inserted?
A temporary table and pl/pgsql should do the trick.
----- Original Message -----
From: Jason Godden <jasongodden@optushome.com.au>
To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / DeleteHi 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:
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 ?
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
484,246 sq mi are needed for 6 billion people to live, 4 persons
per lot, in lots that are 60'x150'.
That is ~ California, Texas and Missouri.
Alternatively, France, Spain and The United Kingdom.
Import Notes
Reply to msg id not found: 000501c366c7$b371a380$e701f00a@240.1.139.196.23.149.50Reference msg id not found: 003a01c366c1$c5705320$e701f00a@240.1.139.196.23.149.50