CODEWITHSUNDEEP
mysql
luqita
luqita

Reputation: 4077

Mysql: ON DUPLICATE KEY UPDATE ALL VALUES?

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?

Upvotes: 7

Views: 7851

Answers (3)

John
John

Reputation: 337

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 . ')';
}

Upvotes: 0

ThiefMaster
ThiefMaster

Reputation: 318478

You can use REPLACE INTO for this purpose:

REPLACE INTO published_books SELECT * from books;

Upvotes: 5

DrinkJavaCodeJava
DrinkJavaCodeJava

Reputation: 808

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

Upvotes: -3

Related Questions