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

  • 2
    What's the purpose of the constraints you've imposed? – Erik Darling May 16 '17 at 16:43
  • These are constraints that i've found with the available query command of the DBMS. – Raymond May 17 '17 at 6:20
  • 1
    Therefore is possible to achieve this result. I agree. What's wrong with my answer? – Evan Carroll May 18 '17 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 '17 at 7:57
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 |
  • This approach didn't work the inline select is not to be allowed. :( – Raymond May 19 '17 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
    Thanks but i already do it (see SqlFiddle) – Raymond May 16 '17 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.