Bulk insert data into empty (but existing) records with SQL

Question

The first table columns (A to G) are already filled with data for each row/record, but the columns H to K have no data in it yet. So I have to add data for these columns, for each individual row in the table (1 to 285, whatever the number is). Columns A to G should remain unaltered!

What SQL query do I use to insert data into existing but empty records? Without overwriting any columns, other than H to K?

I am looking for something that does this:

INSERT INTO `table` (`record_id`, `colA`, `colB`, `colC`, `colD`, `colE`, `colF`, `colG`, `colH`, `colI`, `colJ`, `colK`)

VALUES
    (`auto-increment 1`, `dont-change A`, `dont-change B`, `dont-change C`, `dont-change D`, `dont-change E`, `dont-change F`, `dont-change G`, `new-value H`, `new-value I`, `new-value J`, `new-value K`),
    (`auto-increment 2`, `dont-change A`, `dont-change B`, `dont-change C`, `dont-change D`, `dont-change E`, `dont-change F`, `dont-change G`, `new-value H`, `new-value I`, `new-value J`, `new-value K`),
    (`auto-increment 3`, `dont-change A`, `dont-change B`, `dont-change C`, `dont-change D`, `dont-change E`, `dont-change F`, `dont-change G`, `new-value H`, `new-value I`, `new-value J`, `new-value K`),

All the way to row 285:

    (`auto-increment 285`, `dont-change A`, `dont-change B`, `dont-change C`, `dont-change D`, `dont-change E`, `dont-change F`, `dont-change G`, `new-value H`, `new-value I`, `new-value J`, `new-value K`),

*What SQL query do I use to insert data into existing but empty records?* - erm. An `UPDATE` — Caius Jard, Oct 15 '21 at 16:36
Thanks, but how do I make it certain that it does not overwrite any other columns? — Al John, Oct 15 '21 at 16:36
`UPDATE t SET h = .., i = .. [WHERE ..]` will never update any column A-G — Caius Jard, Oct 15 '21 at 16:38
If sometimes you do and other times you don't want to update, you can conditionally write the same value back (but it is not needed here, it seems). For future ref: `UPDATE t SET x = CASE WHEN y > 10 THEN z ELSE x END, a = CASE WHEN y < 10 THEN b ELSE a END` <-- `x` is updated only if `y` is greater than 10, `a` is updated only if `y` is less than 10. In both cases the value is "not changed" in the ELSE by "writing the current value back into the column". It can, of course, be managed with multiple executions of UPDATE, with different WHERE clauses.. Just mentioning it as "another option" — Caius Jard, Oct 15 '21 at 16:42
Anyhow, have a go at your query, you can always `START TRANSACTION.. UPDATE.. SELECT.. ROLLBACK` if you're nervous - you'll see the results of your update in the select but the change isn't permanent until you change ROLLBACK to COMMIT and run it again. If you get stuck/some syntax error with your UPDATE, post an edit to your Q — Caius Jard, Oct 15 '21 at 16:45

score 1 · Accepted Answer · answered Oct 15 '21 at 16:55

You'd write your query as some set of update statements like:

UPDATE t SET h = .., i = .. WHERE id = 1
UPDATE t SET h = .., i = .. WHERE id = 2

If all the columns get the same values, or blocks of them get the same you can adjust the WHERE clause or omit it

If you want MySQL's help to generate a block of update statements, remember that they're just strings at the end of the day, so you could always do something like this:

SELECT CONCAT('update t set h = .., i = .. where id = ', id) FROM t

It will generate you an update statement per row in the table, and concat the ID onto each one.. You can copy them out of the results grid, paste them into the query editor, tweak them and run. I use this technique quite a lot when I want to make a lot of update statements to a pattern, but then customize some

--

You can also update from another table. See this SO answer: mysql update column with value from another table

score 0 · Answer 2 · answered Oct 15 '21 at 17:11

You can easy change your QUERY to INSERT INTO .. ON DUPLICATE KEY UPDATE ... like this:

INSERT INTO `table` (`record_id`, `colA`, `colB`, `colC`, `colD`, `colE`, `colF`, `colG`, `colH`, `colI`, `colJ`, `colK`)

VALUES
    (`auto-increment 1`, `dont-change A`, `dont-change B`, `dont-change C`, `dont-change D`, `dont-change E`, `dont-change F`, `dont-change G`, `new-value H`, `new-value I`, `new-value J`, `new-value K`),
    (`auto-increment 2`, `dont-change A`, `dont-change B`, `dont-change C`, `dont-change D`, `dont-change E`, `dont-change F`, `dont-change G`, `new-value H`, `new-value I`, `new-value J`, `new-value K`),
    (`auto-increment 3`, `dont-change A`, `dont-change B`, `dont-change C`, `dont-change D`, `dont-change E`, `dont-change F`, `dont-change G`, `new-value H`, `new-value I`, `new-value J`, `new-value K`)

ON DUPLICATE KEY UPDATE

colH=VALUES(colH),
colI=VALUES(colI),
colJ=VALUES(colJ),
colK=VALUES(colK);

Note: if the primary key not identical then you must create a composite unique key over the cols colH to colK .

This Query will insert row that not exists or update rows where a key (like primary or other unique) exists.

Bulk insert data into empty (but existing) records with SQL

2 Answers2