Update non key ID column for multiple rows at once

update

I've inherited of a poorly designed database running on Webdev18.

Most of the tables are not related at the database level but rather in the code.
I ought to insert and update new values in some of the tables, however, due to poor design the table I'm working on doesn't have an explicit primary key, but use the id column as such with no uniqueness being implemented.

--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
---------------

The id is populated via the code for each insert and is not auto-incremented. The function evaluate the current Max() and increment it by 1.
The default value is O.

Each year, we're supposed to register the patients including those of previous year at are still present.Up to now this was done through the application by users this being cumbersome and adding no added value.

So a new record of the same patient is created for each year, thus resulting in duplicate id.

--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
0 |Carine|2017
0 |Prince|2017
---------------

I'm to update the new rows id with new values so as to a result as below:

--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
5 |Carine|2017
6 |Prince|2017
---------------

However, I've got the following constraints in doing so:

Constraints

Approaches that can't be used:

  • No code update,re-writing, and triggers to be added to the table.
  • No looping construct allowed in the SQL Query command interface.
  • No variable declaration in the SQL Query command interface.

RDBMS

WebDev 18 – HyperfileSQL

Therefore is it possible to achieve this result within the constraints mentioned above with simply using an

INSERT INTO ... SELECT FROM... .

Sql Fiddle

Best Answer

You can use the maximum id value and add to each id. This will probably produce a lot of gaps. That can be avoided if the DBMS has window functions - like ROW_NUMBER() - implemented:

INSERT INTO tableX 
    (id, name, year) 
SELECT 
    m.maxid + t.id,       -- m.maxid + ROW_NUMBER() OVER (ORDER BY t.id), 
    t.name, 2017 
FROM
    ( SELECT MAX(id) AS maxid
      FROM tableX
    ) AS m 
  CROSS JOIN
    mytableX AS t 
WHERE 
    t.year = 2016 ;