Re: UPDATE Inserts New Rows

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