CODEWITHSUNDEEP
mysqlselecton-duplicate-key
php_nub_qq
php_nub_qq

Reputation: 16065

MySQL INSERT on duplicate key UPDATE with SELECT

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?

Upvotes: 5

Views: 13996

Answers (3)

Rahul
Rahul

Reputation: 77936

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.

Upvotes: 4

hjpotter92
hjpotter92

Reputation: 80653

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

Upvotes: 1

echo_Me
echo_Me

Reputation: 37243

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

Upvotes: 1

Related Questions