CODEWITHSUNDEEP
mysqlsql
jh95
jh95

Reputation: 399

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)

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270341

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.

Upvotes: 1

Related Questions