Update Sequence of Table entry after ordering by date

Question

sorry for a silly beginners question maybe but I am fairly new to SQL and Databases. I am helping out a non-profit organization and therefore created a table containing all items they pay money on or receive donations etc. So far so good. I index by a column "ItemID" for instance "IT0001", "IT0002", "IT0003" etc. the entries also contain "Date of Purchase".

All was well until the guys from the organization brought in items purchased last year etc.

now I can add them to the database still but while the ItemID counts up the order of the Date is no longer ascending. Which I would like to fix.

However, I fail here and that is why I reach out today. I know how to display by order of the "Date of Purchase" column but I do not know how to update the ItemID field counting up after that.

As an Example:

ItemID Description Date of Purchase IT0001 Item bought 2021-01-01 IT0002 Item2 bought 2021-02-01 IT0003 Item3 bought 2021-01-15

now I want the items to be ordered by "Date of Purchase" - and the ItemID should be updated so that IT0002 becomes IT0003 and vice versa.

I hope I make sense :) Thanks a lot and stay safe

Answer 1

And just to be sure, before I try and run your code....

I know that the ID should usually never change and get your point here totally! However, the database is currently still in it's building-up phase so that is why I want to do that this one time.

Basically, what I want to achieve now is that the Item with the OLDEST "Date of Purchase" has the LOWEST "ItemID" - a few hundred items have been entered by now but unfortunately they were not all received in this order and now some items have a LOWER "ItemID" even though they have a later "Date of Purchase" and I would like to correct that automatically and not update every Item one by one.

I hope this makes more sense now. Thanks a lot again!

Answer 2

Normally, the id of an item (or record) should NEVER change, otherwise you can't reference it in the future and it's harder to find.

If you really REALLY want to change the id of an item (not recommended in this case) then you need to do an UPDATE statement on the table. Like this:

UPDATE TABLE <tablename> SET ItemID=<newId> WHERE ItemID=<oldId>;

Depending on the database you are using you can use some kind of ROW_NUMBER to automatically put a ascending number in the id. There are also scalar functions to fill the number with leading zeros.

Pseudo code (SQL SERVER):

UPDATE TABLE <tablename> SET ItemID = 'IT' || RIGHT(10000 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 4) ORDER BY date_of_purchase ASC;

You could dry test this with the following query and see what would happen:

SELECT date_of_purchase, ItemID as old_id, 'IT' || RIGHT(10000 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 4) as new_id from <tablename> ORDER BY date_of_purchase ASC;

Another thing you have to consider with your id structure is you are now limited to four digits if you want to keep the pattern IT0000. What do you do with 10000th item you purchase?

You should use a simple number for an id, called primary key. This can be kept technical. And then use another field if you want to give it a more human readable id. The primary key should never change.