MySQL on Duplicate Key Update For Multiple Rows Insert in Single Query

MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

Beginning with MySQL 8.0.19 you can use an alias for that row (see reference).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS new
ON DUPLICATE KEY UPDATE
    age = new.age
    ...

---

For earlier versions use the keyword VALUES (see reference, deprecated with MySQL 8.0.20).

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...

MySQL Insert/Update Multiple Rows Using ON DUPLICATE KEY

you forgot the values() key word

INSERT INTO `buoy_stations` (`id`, `coords`, `name`, `owner`, `pgm`, `met`, `currents`)
VALUES 
('00922', 'Point(30,-90)','name 1','owner 1','pgm 1','y','y'),
('00923', 'Point(30,-90)','name 2','owner 2','pgm 2','y','y'),
('00924', 'Point(30,-90)','name 3','owner 3','pgm 3','y','y'),
('00925', 'Point(30,-90)','name 4','owner 4','pgm 4','y','y'),
('00926', 'Point(30,-90)','name 5','owner 5','pgm 5','y','y')
ON DUPLICATE KEY
        UPDATE coords=values(coords), name=values(name), owner=values(owner), pgm=values(pgm), met=values(met), currents=values(currents);

INSERT ON DUPLICATE KEY UPDATE multiple rows, checking duplicate for each row

Your Query is perfectly fine and should exactly produce the result you are expecting.

The Error Message

Unknown column 'user3' in 'field list'

actually means, that your query is considering the Value: user3 as column at some point.

From the query posted, we can't determine the problem, i'll guess you simplified / obfuscated the query in a certain way, but the postet example was correct by accident.

Check, if you are inserting your user value for user3 enclosed by backticks, like

`user3`

instead of

'user3'

(Back-ticking is used for COLUMN names, not values, which then would produce exactly the error message you are facing)

MySQL INSERT multiple rows ON DUPLICATE KEY UPDATE ... WHERE EXISTS(subquery)

Personally, I would maintain access rights in the application layer and only execute the INSERT query if the user had those access rights. If you must do it solely in MySQL your last query is pretty much your only alternative, although it can be simplified (and made more efficient) by using a CROSS JOIN; this way you don't need the dummy column as all rows of the UNION table will be automatically joined to the single row from the access check (assuming it returns data). Note in my query I've added the id key column for demo purposes:

INSERT INTO mytable (id, col1, col2, col3)
SELECT t1.*
FROM ((
       SELECT 2 id, 1 col1, 2 col2, 3 col3
       UNION SELECT 1, 4, 5, 6
       UNION SELECT 3, 7, 8, 9
      ) t1
      CROSS JOIN (SELECT 1 FROM accesstable WHERE user_id=114 LIMIT 1) t2
     )
ON DUPLICATE KEY UPDATE
col1=VALUES(col1), col2=VALUES(col2), col3=VALUES(col3)

Demo on dbfiddle

INSERT ON DUPLICATE KEY UPDATE multiple rows at once

answer by @Solarflare:

Use ...VALUES ? ON DUPLICATE... (without brackets) and [valuesArray] (with brackets).

MySQL ON DUPLICATE KEY UPDATE wth JOIN for multiple rows insert in single query

You should use Union all:

INSERT INTO beautiful (employee,name, age)
SELECT * from 
FROM (
select '1','Helen', '24' 
union all
select '2','Katrina', '21' 
) beautiful
JOIN employees on employees.id = beautiful.employee and employee.company = '$company'
ON DUPLICATE KEY UPDATE
name= VALUES(name), age = VALUES(age)

and you also can use PHP to make that union all syntax - eg: implode(' union all', $array_of_selects)

INSERT INTO .. ON DUPLICATE KEY UPDATE for multiple items

Use the VALUES() function

INSERT INTO t (t.a, t.b, t.c)
VALUES ('key1','key2','value'), ('key1','key3','value2')
ON DUPLICATE KEY UPDATE
t.c = VALUES(t.c)

see http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Mysql insert multiple rows, if duplicate value column then update

You must create unique index which will detect the duplication:

CREATE UNIQUE INDEX idx ON test (customer_id, textkey);

Now you can use INSERT .. ODKU:

INSERT INTO test (customer_id, textkey, value) VALUES
(1, 'text1', 'valueX'), 
(1, 'text3', 'valueY'), 
(1, 'text5', 'value5'), 
(1, 'text6', 'value6')
ON DUPLICATE KEY UPDATE
value = VALUES(value);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6dcc351706f574f4c9f13c1fc95b9225

SQL Insert multiple record while using ON DUPLICATE KEY UPDATE

Your query is basically correct, just get rid of the intermediate ON DUPLICATE KEY.... There is no need for a derived table because you are not referencing columns from the union.

INSERT INTO tableName (COLUMN_1, COLUMN_2, COLUMN_3) 
SELECT 'test8', 'test9', 'test10'
UNION ALL
SELECT 'test4', 'test5', 'test6'
UNION ALL
SELECT 'test9', 'test5', 'test6'
ON DUPLICATE KEY UPDATE COLUMN_1='new', COLUMN_2='new', COLUMN_3='new';

The problem you are going to run into is if you get more than one duplicate key on the INSERT. In that case the UPDATE will attempt to set two rows to have the same key ('new') and the INSERT will fail. You could potentially work around this by changing the query so that the UPDATE includes part of the old column value. In this case since you'll be referring to a column value you will need a derived table:

INSERT INTO tableName (COLUMN_1, COLUMN_2, COLUMN_3) 
SELECT * FROM (
    SELECT 'test8', 'test9', 'test10'
    UNION ALL
    SELECT 'test4', 'test5', 'test6'
    UNION ALL
    SELECT 'test1', 'test5', 'test6') AS dt
ON DUPLICATE KEY UPDATE COLUMN_1=CONCAT('new', COLUMN_1), COLUMN_2='new', COLUMN_3='new';

Updated SQLFiddle

---

Related Topics