Update non key ID column for multiple rows at once

Question

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

Answer 1

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 ;

Answer 2

Why not simply use the existing unsecure (concurrency wise) technique of select max(id) + 1 for this, too?

insert into Patients(id, `Name`, `year`)
select 
id + (select max(id) + 1 from Patients),
`Name`, `year`+1 from Patients;

?
Results in

| id |   Name | year |
|----|--------|------|
|  0 | Carine | 2016 |
|  1 |   Karl | 2016 |
|  2 | Prince | 2016 |
|  3 | Carine | 2017 |
|  4 |   Karl | 2017 |
|  5 | Prince | 2017 |

Answer 3

I'm suppose to duplicate a number of rows as new entries(with a different year) and update the ID's.

If IDs are a property of a sequence, you can do..

INSERT INTO mytable (foo, bar, year)  -- id col omitted
SELECT foo, bar, 2017
FROM mytable
WHERE year = 2016;

This will result in the new rows getting new ids, having the same data, and having a new year.