Re: Bulk Insert / Update / Delete
От | Ron Johnson |
---|---|
Тема | Re: Bulk Insert / Update / Delete |
Дата | |
Msg-id | 1061311505.18259.359.camel@haggis обсуждение исходный текст |
Ответ на | Re: Bulk Insert / Update / Delete ("Philip Boonzaaier" <phil@cks.co.za>) |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: