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**](http://stackoverflow.com/a/14701250/342740) — Prix, Jul 14 '13 at 11:20
@prix I don't see how this can apply to my issue, I only have 1 table — php_nub_qq, Jul 14 '13 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, Jul 14 '13 at 11:25
possible duplicate of [How to INSERT INTO...SELECT with ON DUPLICATE KEY](http://stackoverflow.com/questions/14700664/how-to-insert-into-select-with-on-duplicate-key) — hjpotter92, Jul 14 '13 at 11:25

score 4 · Accepted Answer · answered Jul 14 '13 at 11:28

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.

score 1 · Answer 2 · answered Jul 14 '13 at 11:24

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

score 1 · Answer 3 · answered Jul 14 '13 at 11:24

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 '13 at 11:24

hjpotter92

78,589
36
144
183

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 Jul 14 '13 at 11:35
Also this doesn't seem to add a new row if the conditions are not met :/ – php_nub_qq Jul 14 '13 at 11:38

MySQL INSERT on duplicate key UPDATE with SELECT

3 Answers3