Re: Bulk Insert / Update / Delete

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 / 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 ?

--
-----------------------------------------------------------------
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.