Mysql insert with value from target table

Question

So I need to select a value from my table and modify it slightly to use as a value in a new row in the same table when inserting. See below:

INSERT INTO reservationbody(
    reservationid,
    driverid,
    tripnumber,
    fromlocation,
    tolocation
)
VALUES(
    1,
    2,
    (
        SELECT
           MAX(tripnumber) + 1
        FROM reservationbody
        WHERE reservationid = 1
    ),
    'here',
    'there'
)

I'm getting the following error:

You can't specify target table 'reservationbody' for update in FROM clause

I've had a look at other questions and have found plenty of questions on this error but they all seem to be about updates and deletes and all require PK values that already exist. None deal with inserting.

Basically what I'm expecting here is that if SELECT MAX(tripnumber) FROM reservationbody where reservationid = 1 returns a value of 4 then the insert should add a new row with a tripnumber value of 5

Can anyone suggest how I can accomplish this?

John Woo · Accepted Answer · 2012-09-10 09:30:25Z

2

Have you tried (I know you can do this in MS SQL):

INSERT INTO reservationbody(reservationid,driverid,tripnumber,
                                       fromlocation,tolocation)
SELECT 1, 2, MAX(tripnumber) + 1, 'here', 'there'
FROM reservationbody
WHERE reservationid = 1

edited Sep 10, 2012 at 9:30

John Woo

265k70 gold badges509 silver badges500 bronze badges

answered Sep 10, 2012 at 9:26

Andrew Rayner

4242 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ortund Over a year ago

Thanks :) had a problem with date format going in (got an out of range error 1264) but it's sorted

Collectives™ on Stack Overflow

Mysql insert with value from target table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related