Little SQL annoyance in SA12.0.1.x. Reduced to clarify the problem.
I'd like to use this to copy a row in the same table. The 2 fields shown are part of the PK. This does not work because the aliasing of PROGRAM_VERSION + 1 as PROGRAM_VERSION generate a where clause always saying x = x+1 whic of course is never true, so the statement fails. I tried alias names but that breaks AUTO NAME ... unfortunately. And there is no option DISCARD SUPERFLUOUS ..
Any idea how I can do it nevertheless elegantly in one statement?
Try using "and PROGRAMS.PROGRAM_VERSION = 2".
CREATE TABLE PROGRAMS ( PROGRAM_GUID UNIQUEIDENTIFIER NOT NULL, PROGRAM_VERSION INTEGER NOT NULL, PRIMARY KEY ( PROGRAM_GUID, PROGRAM_VERSION ) ); INSERT PROGRAMS VALUES ( 0x15ff08574671425fa02aab2cc84a25e5, 2 ); insert into PROGRAMS with auto name select PROGRAM_GUID, PROGRAM_VERSION + 1 as PROGRAM_VERSION from PROGRAMS where PROGRAM_GUID = 0x15ff08574671425fa02aab2cc84a25e5 and PROGRAMS.PROGRAM_VERSION = 2; SELECT * FROM PROGRAMS ORDER BY PROGRAM_GUID, PROGRAM_VERSION; PROGRAM_GUID,PROGRAM_VERSION 0x15ff08574671425fa02aab2cc84a25e5,2 0x15ff08574671425fa02aab2cc84a25e5,3
answered 13 Jun '14, 14:08
While I would prefer Breck's solution here, I'd like to point to a different approach:
You can use a derived table when you need an alias in the select list and do not want to use that alias in the original query because of scope issues/irritations, such as:
As you can see, the PROGRAM_VERSION column in the WHERE condition does not have to be qualified here as it is not already aliased in the inner query block...