MySQL INSERT on duplicate key UPDATE with SELECT

Question

I have a query like the following

INSERT INTO connections (`c_id`,`in`,`out`,`ip`,`userID`) 
VALUES ( 
    ( 
        SELECT c_id 
        FROM connections 
        WHERE (a bunch of conditions) 
        ORDER BY c_id DESC LIMIT 1 

    ),
    '1373799802',
    0,
    INET_ATON('127.0.0.1'),
    4

) 
ON DUPLICATE KEY UPDATE `out` = 1

Which throws the following error

1093 - You can't specify target table 'connections' for update in FROM clause

Obviously I can't have a SELECT clause within the insert into on duplicate update syntax, but I'd really rather do that instead of have 2 queries running. Can anyone tell me how I can do this?

possible duplicate of How to INSERT INTO...SELECT with ON DUPLICATE KEY — Prix
– Prix, Commented Jul 14, 2013 at 11:20
@prix I don't see how this can apply to my issue, I only have 1 table — php_nub_qq
– php_nub_qq, Commented Jul 14, 2013 at 11:23
You don't need 2 tables however your query is wrong and you can see from the example there what is. — Prix
– Prix, Commented Jul 14, 2013 at 11:25
possible duplicate of How to INSERT INTO...SELECT with ON DUPLICATE KEY — hjpotter92
– hjpotter92, Commented Jul 14, 2013 at 11:25

Rahul · Accepted Answer · 2013-07-14 11:28:40Z

4

Try like this instead:

INSERT INTO connections (`c_id`,`in`,`out`,`ip`,`userID`) 
VALUES ( 
    ( 
SELECT p.c_id 
        FROM (select * from connections) p 
        WHERE (a bunch of conditions) 
        ORDER BY p.c_id DESC LIMIT 1 

    ),
    '1373799802',
    0,
    INET_ATON('127.0.0.1'),
    4

) 
ON DUPLICATE KEY UPDATE `out` = 1

This issue seems due to a bug in mysql version 4.1.7 which states that

you can't update the same table which you use in the SELECT part

see Here

Not sure if this is the same version you are using as well.

answered Jul 14, 2013 at 11:28

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Comments

echo_Me · Accepted Answer · 2013-07-14 11:24:48Z

1

try this

    INSERT INTO connections (`c_id`,`in`,`out`,`ip`,`userID`) 
    SELECT c_id ,'1373799802', 0, INET_ATON('127.0.0.1'),4
    FROM connections 
    WHERE (a bunch of conditions) 
    ORDER BY c_id DESC LIMIT 1 

    ON DUPLICATE KEY UPDATE `out` = 1

answered Jul 14, 2013 at 11:24

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Comments

hjpotter92 · Accepted Answer · 2013-07-14 11:24:55Z

1

The following code inside your query:

SELECT c_id 
FROM connections 
WHERE (a bunch of conditions) 
ORDER BY c_id DESC
LIMIT 1

actually results in a table and not a single value. For a successful attempt, try this:

INSERT INTO connections (`c_id`,`in`,`out`,`ip`,`userID`) 
    SELECT  c_id,
            '1373799802',
            0,
            INET_ATON('127.0.0.1'),
            4
    FROM connections 
    WHERE (a bunch of conditions) 
    ORDER BY c_id DESC LIMIT 1 
ON DUPLICATE KEY 
UPDATE `out` = 1

answered Jul 14, 2013 at 11:24

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

2 Comments

php_nub_qq Over a year ago

How come does the first query result in a table when I only select a single column and a limit of 1 result? Also can you please explain why I must have all values as a result set from a select clause instead of how I've written it?

php_nub_qq Over a year ago

Also this doesn't seem to add a new row if the conditions are not met :/

Collectives™ on Stack Overflow

MySQL INSERT on duplicate key UPDATE with SELECT

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related