Menu
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?
808
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.
Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
We can perform MySQL UPSERT operation mainly in three ways, which are as follows: UPSERT using INSERT IGNORE. UPSERT using REPLACE. UPSERT using INSERT ON DUPLICATE KEY UPDATE.
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.
119
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
