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

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`),

CodePudding user response：

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

CodePudding user response：

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.