Nesting a SELECT in an UPDATE in a SELECT

[pennstump]

Nesting a SELECT in an UPDATE in a SELECT" == "Selecting the updated record from an update with a nested select"

I've been searching for most of the morning without any luck on this topic. I'm not sure if it's even entirely possible with SQL, but I would like to be able to SELECT a row of work in my table, based on my row status codes. I would then like to immediately set the status code to '1' and select the row that was updated.

I have the statement where I would "reserve" the work row:

UPDATE workTable SET pstat=1,pchby='user',pchtm=CURRENT_TIMESTAMP WHERE pstat=0 AND preqst=( SELECT preqst FROM workTable WHERE pstat=0 AND preqst=80650 OR preqst=80658 OR preqst=81284 LIMIT 1 );

I would like to somehow return the preqst of the row that I set the status code = '1'. Any help would be appreciated. I would like to stick as close to standard, cross-platform SQL as possible, if possible. Would "SELECT FOR UPDATE" do me any good? What about a temporary variable?

Thanks.

 

[JarlH]

The first thing get rid of, if you want portable code, is the "LIMIT 1" part of the sub-select. LIMIT is not ANSI SQL and only supported by a few DBMS products. Since you want that sub-select to return exactly one row, use for example MIN (or MAX), i.e. SELECT MIN(preqst) FROM...

As UPDATE doesn't return any data, I suppose you have to do the task in two steps. First SELECT the preqst, then do the UPDATE. (Just make sure you keep them in the same transaction.)

SELECT FOR UPDATE wont help you because that involves cursors and UPDATE WHERE CURRENT, which will be unnecessary complicated in this case.

(BTW, Pennstump... Swedish I suppose?)

 

[juliane26]

But, with the new SQL Standard something else was introduced: SELECT over INSERT / UPDATE / DELETE

It would look something like

SELECT xid
FROM NEW TABLE(
INSERT INTO xtab(..) VALUES(...))

one problem: there are generated identity column. when inserting a row, you need the new identity value as a reference. In the past, this was usually solved using a sequence, in the future this is not needed anymore.

This is SQL2005 or SQL2003, I am not sure here. Whether it is supported depends on your DBMS. DB2 UDB on LUW has it Version 8.2, again I am not sure about DB2 z/OS or Oracle.

Have a look also at UPDATE / INSERT / DELETE over SELECT.

I hope that helps. Please ask, if not clear.

Juliane

 

[JarlH]

SQL-2005 is short for the Microsoft product SQL Server 2005.

SQL-2003 is short for the ISO/ANSI SQL standard ISO/IEC 9075:2003. (Just like SQL-92 meant ISO/IEC 9075:1992.)

I searched the SQL-2003 documents for "SELECT over INSERT", but I couldn't find anything. Is there another term I should search for?

 

[juliane26]

Not quite right, I am talking about the ANSI SQL standard, and there is SQL92 (short for ANSI SQL standard from 1992), then 99, 2003, ... on ansi.org they are available for purchase
Still - we are in the ANSI SQL forum so I expected that to be clear, sorry. I guess it was irritating.


well, I gave some syntax above, and this is from a presentation I got about the new SQL standard last spring.

It is documented with DB2 V8:

http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0000875.htm

look for subselect -> from-clause -> table-reference -> data-change-table-reference




Juliane

 

[JarlH]

Juliane,

I read the DB2 documentation you referred to, but I don't think thats a part of the ANSI SQL standard. I did even try to write an SQL statement like that, but I couldn't get it thru the SQL-2003 Validator (

http://developer.mimer.com/validator/).

(Maybe I'm just too tired and need to go home and rest. Thank God, it's Friday...)

 

[juliane26]

SQL Server 2000 was only SQL92 compliant, I guess 2003 can manage SQL99 but not more.
Don't expect it to be working there, it does not exist. I mean the standard wasn't even defined when the product came out ....

As I said SQL standard definitions like SQL2003 is the ANSI SQL standard definition defined by the ANSI SQL Standard Committee (

http://www.ansi.org

-> search for SQL and you get all the parts of SQL standard to purchase)
Sorry, but It has NOTHING to do with the name of the Microsoft database or any other product.

I hope its clear now.

Pennstump: for your question: the availability of this depends very much on which DBMS system you are on.

Juliane