How to copy a row in the same table with special conditions?

[Former Member]

hi there

Little SQL annoyance in SA12.0.1.x. Reduced to clarify the problem.

Consider this:

insert into PROGRAMS 
  with auto name
select PROGRAM_GUID, 
       PROGRAM_VERSION + 1 as PROGRAM_VERSION,
       .....  
  from PROGRAMS 
 where PROGRAM_GUID = '....' 
   and PROGRAM_VERSION = 2


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?

[Breck_Carter · Accepted solution]

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

[Former Member · Accepted solution]

Hi Breck

Well, if the Chef (german for Boss) tells me that it's that simple, it certainly must be ...

Arrg, I seem to got rusty during the last few years ... never had to work with SA for years now ... you catch me embarrassed ... 🙂

Regards, Martin

[Breck_Carter · Accepted solution]

The "scope of names in SQL Anywhere" is certainly not simple... it is far more intricate than in, say, application programming languages. AFAIK the scope rules aren't documented anywhere, but such documentation would probably be impossible to remember... it is sufficient to remember that the rules ARE complex, and when faced with a situation like yours, "try things"... like qualifying with the table name if you don't like the alias name.

Personally, I was only 50% sure PROGRAMS.PROGRAM_VERSION would work 🙂

If anyone thinks the rules ARE NOT complex, consider this: "PROGRAM_VERSION + 1 as PROGRAM_VERSION" refers to two different PROGRAM_VERSION names, and the first one is the same as PROGRAMS.PROGRAM_VERSION without the need for the table name. HOWEVER, elsewhere (like line 2 below) omitting the table name gives the alias name...

select PROGRAM_VERSION + 1 as PROGRAM_VERSION,
       PROGRAM_VERSION,
       PROGRAMS.PROGRAM_VERSION
  from PROGRAMS 
 where PROGRAM_GUID = 0x15ff08574671425fa02aab2cc84a25e5 
   and PROGRAMS.PROGRAM_VERSION = 2;

PROGRAM_VERSION,PROGRAM_VERSION,PROGRAM_VERSION
3,3,2

Local variables and FOR loop alias names add further complexity, as do CREATE VARIABLE names, nested BEGIN blocks and parameter names.

This complexity is THE ONLY REASON that I append @ to all variable names, including FOR loop alias names which are really local variables IMO... over the years that convention has avoided many problems (at least, I think it has 🙂

[VolkerBarth]

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:

insert into PROGRAMS 
  with auto name
select PROGRAM_GUID, 
       PROGRAM_VERSION + 1 as PROGRAM_VERSION
  from
(select PROGRAM_GUID, 
        PROGRAM_VERSION
   from PROGRAMS 
  where PROGRAM_GUID = 0x15ff08574671425fa02aab2cc84a25e5 
    and PROGRAM_VERSION = 2) dt;


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...