insert a row with one column value based on last record

Question

I need to insert a newly added product in table. But to do so I need to check SKU of last inserted product and increase it by 1 and insert it with new product details. SKU is something like SKUNXXXXX - X is a digit. Changing table structure is not possible.

Possible solutions that I can think of is

Get last row using order by and limit 1.
replace "SKUN" with empty string and increase the number by 1
Insert record with product details and incremented SKU value

But this situation may create a problem(though I am not sure about it). Problem is - what if just after fetching last record and before inserting the new product details, another request comes in and gets the same last record? In this case both of the products have same SKU.

Please let me know how to solve this situation.

in this case, may be, u should use table locking or transactions — M0rtiis, Jul 27 '15 at 05:36
Why don't you use an auto-increment column instead of a character string? — Barmar, Jul 27 '15 at 06:14
@Barmar - I know auto-incremented column is the best way but I can't change it know as previous developer has used it in many places and it will break things. Thanks for your suggestion. — Abani Meher, Jul 27 '15 at 06:17
BTW (about previous developer) its not a big deal to do AI there. use fulltext search on your project and change things, +1 sql migration — M0rtiis, Jul 27 '15 at 06:35

score 0 · Answer 1 · edited May 23 '17 at 11:58

One option would be to create an auto increment id column and then use this to create the SKU for each product as it gets inserted. A trigger, which would fire after an INSERT, could then be used to assign the SKU for the newly inserted product. But alas, MySQL does not allow a trigger to modify the table which fired it.

However, there is a potentially easy workaround here. If your SKU values will really always have the format SKUNXXXXX, where XXXXX is some number, then you can simply add an auto increment column, and create the SKU value on the fly when you query for it. So you might create a table called products with the following columns:

CREATE TABLE products
(
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(55),
    ...
);

Everytime you do an INSERT on products, the id column will automatically be incremented by MySQL. When you want to get the SKU out, you can simply do a query like this:

SELECT id, name, CONCAT('SKUN', id) AS `SKU`
FROM products
WHERE name = 'someproduct'

If you want the SKU number to not be forever (i.e. once assigned to a given product, it can be reassigned to another new one), then you might consider resetting the auto increment id column, q.v. this SO post for more information.

insert a row with one column value based on last record

1 Answers1