HowTo: Using SQL get Updated/Deleted/Inserted rows ID/content

[mLipok]

Some time ago once forum memeber asked about how to get updated rows ID.

In PostgreSQL you can do it like this.

UPDATE "customers" SET "first_name" = 'Jan'  WHERE "last_name" = 'Kowalski'  RETURNING *
UPDATE "customers" SET "first_name" = 'Jan'  WHERE "last_name" = 'Kowalski'  RETURNING "customer_id"

 

EDIT:

You can also use RETURNING clause with INSERT and DELETE:

https://www.postgresql.org/docs/9.0/static/sql-update.html
https://www.postgresql.org/docs/8.2/static/sql-insert.html
https://www.postgresql.org/docs/8.2/static/sql-delete.html
 

 

Edited June 14, 2016 by mLipok

[mLipok]

In MS SQL this is also possible with OUTPUT clause:

https://msdn.microsoft.com/pl-pl/library/ms177564(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/ms177564.aspx
https://blogs.msdn.microsoft.com/sqltips/2005/06/13/output-clause-in-insertupdatedelete-statements/

http://4programmers.net/SQL/Instrukcja_OUTPUT_w_SQL_Server

Edited June 14, 2016 by mLipok

[jchd]

It's possible to get that done reliably and portably while remaining within the realm of pure SQL. Returning, Output and YouNameIt are clearly outside relational algebra and qualify as proprietary extensions acting as syntactic sugar.

Two obvious ways to perform the same thing, portably:

begin immediate;
create temp table UpdatedCustomers as select * from customers WHERE "last_name" = 'Kowalski';
UPDATE "customers" SET "first_name" = 'Jan' WHERE "last_name" = 'Kowalski';
select * from UpdatedCustomers;
drop table UpdatedCustomers;
commit;

create temp table UpdatedCustomers ("customer_id" integer primary key);
create trigger trUpdateCustomers after update of "customer_id" on "customers"
  begin
    insert or ignore into UpdatedCustomers values (old."customer_id");
  end;
--  .../...
UPDATE "customers" SET "first_name" = 'Jan'  WHERE "last_name" = 'Kowalski'  RETURNING "customer_id";     -- done
select * from UpdatedCustomers;
delete from UpdatedCustomers;

Also I question the semantics of the second proposed statement in the PostgreSQL example:

UPDATE Items SET UnitPrice = UnitPrice * 1.05 WHERE ItemFamilly = 'DVD' RETURNING UnitPrice;

Would that return old or adjusted UnitPrices?

[mLipok]

  On 6/14/2016 at 10:01 PM, jchd said:

Would that return old or adjusted UnitPrices?

Expand  

Adjusted, old are a past.