From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Ruben Oliveira <ruben_dig(at)netcabo(dot)pt> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: UPDATE Inserts New Rows |
Date: | 2005-05-11 12:18:20 |
Message-ID: | 4281F80C.7090402@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ruben Oliveira wrote:
> I'm having an unexpected behavior from an UPDATE query :
> I expected only updates to existing rows but it is INSERTING new rows
> when there isn't a PK
Hmm - doesn't sound likely. Especially if you don't supply a primary key.
> There are no triggers,views or rules associated with the tables ....
> and to make things worse I have a similar query to another table where
> the UPDATE works as expected
> and if I want to make INSERTs I have to make them :)
> It seems the UPDATE is working like the Mysql REPLACE ... I tested in
> Postgresql 7.3 in Linux and Postrgresql 8.0 in Win32
> the new row in table084 has the field1,field2 from the where clause in
> the UPDATE
> the default values for field3 ,field4 and the SET values from the UPDATE
> to field5, field6, field7
> UPDATE table084
> SET field5=table090.field9,
> field6=table090.field11,
> field7=date_o(now())
> WHERE table084.field1=table088.field6
> AND table084.field2=table090.field4
> AND table088.field1='DOC_TITLE'
> AND table088.field2=123456
> AND table088.field1=table090.field1
> AND table088.field2=table090.field2;
You should really have a FROM clause in this update - I'd have thought
you'd get an error with this in version 8.0
Could you show the actual problem:
1. BEGIN
2. SELECT oid,cmin,cmax,* FROM table084 WHERE <conditions returning no rows>
3. Run above query
4. SELECT oid,xmin,xmax,cmin,cmax,* FROM table084 WHERE <same conditions
as above>
5. SELECT oid,xmin,xmax,cmin,cmax,* FROM table084 WHERE <row that has
been updated properly>
6. ROLLBACK
That way we can see that a row has really been inserted and which
transaction did it.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Mage | 2005-05-11 12:19:17 | plpython setof row |
Previous Message | Patrick.FICHE | 2005-05-11 12:01:15 | PsqlODBC / Unix |