Mysql: ON DUPLICATE KEY UPDATE ALL VALUES?

Question

Is there any easy way to update all values after a duplicate key? For example:

INSERT INTO published_books
           SELECT * FROM books
           WHERE book_id = book_id
           ON DUPLICATE KEY UPDATE ?everything?

The table has around 50 columns and updating each would be painful. Any ideas?

for the coder... and for the eyes of the other coders :) – luqita Jun 29 '12 at 03:48 — luqita, Jun 29 '12 at 03:48

score 5 · Answer 1 · answered Jun 29 '12 at 13:53

5

You can use REPLACE INTO for this purpose:

REPLACE INTO published_books SELECT * from books;

answered Jun 29 '12 at 13:53

ThiefMaster

310,957
84
592
636

2

Please be aware when using `REPLACE INTO` that it actually deletes and recreates a row with only the information in the insert. It does not update the row as `ON DUPLICATE KEY UPDATE` does. For more info see here: http://code.openark.org/blog/mysql/replace-into-think-twice – George May 22 '13 at 20:11

score 0 · Answer 2 · answered Oct 18 '13 at 10:14

If you can generate the query, you could use something like this (PHP):

$fields = array(
    'field_1',
    'field_2',
    'field_3'
);
$query = '
    INSERT INTO table (
    ' . implode(', ', $fields) . '
    ) SELECT
        -- ...
    ON DUPLICATE KEY UPDATE ';
foreach ($fields as $i => $field) {
    $query .= ($i > 0 ? ', ' : '') . $field . '=VALUES(' . $field . ')';
}

score -3 · Answer 3 · edited Jun 29 '12 at 13:50

-3

INSERT INTO published_books(col1, col2) 
VALUES (’xxx’, ‘yyy’) ON DUPLICATE KEY UPDATE col1 = VALUES(col1)

edited Jun 29 '12 at 13:50

Taryn

242,637
56
362
405

answered Jun 29 '12 at 00:31

DrinkJavaCodeJava

812
8
22

2

how would you do the same thing with 50 cols? col1, col2... col50? – luqita Jun 29 '12 at 03:48

Mysql: ON DUPLICATE KEY UPDATE ALL VALUES?

3 Answers3

Linked