Re: Bulk Insert / Update / Delete

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

----- Original Message -----
From: Jason Godden <jasongodden(at)optushome(dot)com(dot)au>
To: Philip Boonzaaier <phil(at)cks(dot)co(dot)za>; <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, August 19, 2003 4:42 PM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete

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:
> 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(at)cks(dot)co(dot)za(dot) 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

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

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(at)cks(dot)co(dot)za(dot) 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.