1

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

4
  • 2
    What's the purpose of the constraints you've imposed? May 16, 2017 at 16:43
  • These are constraints that i've found with the available query command of the DBMS.
    – Raymond
    May 17, 2017 at 6:20
  • 1
    Therefore is possible to achieve this result. I agree. What's wrong with my answer? May 18, 2017 at 19:32
  • The Id column is not auto-incremented and not unique . Therefore i end up with duplicate Ids which im trying to avoid.
    – Raymond
    May 19, 2017 at 7:57

3 Answers 3

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 ;
1

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 |
1
  • This approach didn't work the inline select is not to be allowed. :(
    – Raymond
    May 19, 2017 at 14:18
0

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.

1
  • 1
    Thanks but i already do it (see SqlFiddle)
    – Raymond
    May 16, 2017 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.