Thread: Getting actual number of rows updated

Is it possible to have PG report the actual number of
rows that actually CHANGED in an update command? e.g.

UPDATE items set name=replace(name,'abc','def');
UPDATE 9000 -- Actually only 3 were changed

rather than update reporting all rows have been
"updated"?

Thanks,
CSN



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

All the raws are updated if there is no WHERE clause.
If you want the UPADATED rows, just specify the WHERE clause and it will
really improve your performance...

EX : UPDATE itemp SET name = replace( name, 'abc', 'def' ) WHERE name LIKE
'%abc%'

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------




-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of CSN
Sent: lundi 8 août 2005 09:33
To: pgsql-general@postgresql.org
Subject: [GENERAL] Getting actual number of rows updated


Is it possible to have PG report the actual number of
rows that actually CHANGED in an update command? e.g.

UPDATE items set name=replace(name,'abc','def');
UPDATE 9000 -- Actually only 3 were changed

rather than update reporting all rows have been
"updated"?

Thanks,
CSN



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs


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

Am Montag, den 08.08.2005, 00:32 -0700 schrieb CSN:
> Is it possible to have PG report the actual number of
> rows that actually CHANGED in an update command? e.g.
>
> UPDATE items set name=replace(name,'abc','def');
> UPDATE 9000 -- Actually only 3 were changed
>
> rather than update reporting all rows have been
> "updated"?

Since you have no where clause all rows have indeed been
updated. No matter if you write the same data which is
already in the row the update happens nevertheless.

(How would PG know? Also there might be a trigger or
something. So best is to just do what you request)

Add a where clause like WHERE name ~'abc';

And both you and PG will be happy :-)
--
Tino Wildenhain <tino@wildenhain.de>