Re: Bulk Insert / Update / Delete

Thanks Jason.

The UPDATE part works fine. However, INSERT still gives problems. For now,
I'm inserting hard coded values, and not from one table into the other.
I use

UPDATE telephones SET
telephone_type='CELL',
telephone_number=836789012
WHERE data_set='AA'
AND account_number=8
AND dependant_number=1
AND sub_entity='pers'
AND sub_occur=1

INSERT INTO telephones
VALUES ('AA',8,1,'pers',1,CELL,836789012);

even if I were getting these values from another table, I'd still have to
somehow determine if the data EXISTS in the telephones table before deciding
to UPDATE, or INSERT.

It looks like this is just not possible in a SQL query on its own, but has
to be done in a higher level language where the condition may be
successfully processed - i.e. SELECT * WHERE <same WHERE as above>
and then check IF EXISTS, if so, UPDATE, if not INSERT.

Or would I be able to achieve such functionality by writing a Function ?

Regards

Phil

----- Original Message -----
From: Jason Godden <jasongodden(at)optushome(dot)com(dot)au>
To: Philip Boonzaaier <phil(at)cks(dot)co(dot)za>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, August 20, 2003 10:07 AM
Subject: Re: [GENERAL] Bulk Insert / Update / Delete

Hi Philip,

See:

http://www.postgresql.org/docs/7.3/static/functions-subquery.html

..for starters.

Essentially, to perform the operation atomically I'd use:

begin;

update <table> set <cols> = <values>, ... where exists (select
<corresponding
columns> from <table2> where <table1>.<col> = <table2>.<col> (and).. etc..);

(actually i'd probably use a the from extension here ^^^^ , see example
below)

insert into <table> <columnlist> select <columns> from <table2> where not
exists (select <corresponding columns> from <table1> where <table2>.<col> =
<table1>.<col> (and).. etc..);

commit;

because it's wrapped in a transaction both queries have to work or it's all
rolled back. This example only applies to comparing two tables. You can
specify a value list if need be.

As an actual example:

begin;

update table1 set col1 = table2.col1, col2 = table2.col2 from
table2 where table2.key = table1.key;

(whatever your key may be..)

insert into table1 (col1,col2) select col1,col2 from table2 where not exists
(select col1,col2 from table1 where table1.col1 = table2.col1 and
table1.col2
= table2.col2);

(in this ^^^ I'm assuming your keys are col1 and col2 and so it's not
consistent with the update but you get the idea.

commit;

Rgds,

Jason

On Wed, 20 Aug 2003 01:03 pm, 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
>
> ----- 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 ?
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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.