Re: DB2-style INS/UPD/DEL RETURNING

On Sun, 2006-03-12 at 11:11 -0500, Jonah H. Harris wrote:
> I was talking with Jonathan Gennick about the INS/UPD/DEL RETURNING
> stuff, and he recommended looking into the way DB2 handles similar
> functionality. After looking into it a bit, it's more inline with
> what Tom's suggestion was regarding a query from the operation rather
> than returning the values in the manner currently required.
>
> Here's DB2's syntax... does anyone have any familiarity with it?
>
> Simply put, it's sort-of like:
>
> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)
>
> I'd like to hear from anyone that's used it to see if it really is
> better... logically it seems nicer, but I've never used it.

Hmmm...well, IMHO either syntax is fairly contrived, but the DB2 syntax
does seem a more meaningful way of doing this. It is pretty obscure
though...most DB2 people don't know the above syntax because its new in
DB2 8.1

The DB2 syntax allows you to more easily do things like a simultaneous
copy-and-delete from a holding table into a main table, e.g.

INSERT INTO MAINTABLE
SELECT * FROM NEW TABLE (DELETE FROM HOLDINGTABLE WHERE ...)

Thats quite a nice performance trick I've used to save doing separate
INSERT and DELETE tasks on a busy table.

The Oracle syntax reads less well for that type of task.

Best Regards, Simon Riggs