jh95 jh95 - 18 days ago 7
MySQL Question

Is there anyway to insert data into table based off a select query?

I am trying to insert a value into a MySQL database, and I want the value to be the result of a query + 1. Is it there any way to do this similar to my posted code?

INSERT INTO transaction (transactionId,userId)
VALUES
(select (
SELECT max(transactionId) FROM `transaction` WHERE userId = 26) + 1),26)

Answer

You can phrase the query as as insert . . . select:

INSERT INTO transaction (transactionId, userId)
    SELECT max(transactionId) + 1, 26
    FROM transaction
    WHERE userId = 26;

Actually, this is safer:

INSERT INTO transaction (transactionId, userId)
    SELECT coalesce(max(transactionId) + 1, 1), 26
    FROM transaction
    WHERE userId = 26;

But, either version still has race conditions. You should really set up transactionId to be an auto-increment column across all users. This guarantees uniqueness on each row and doesn't suffer from race conditions.

Comments