CODEWITHSUNDEEP
mysql
oxk4r
oxk4r

Reputation: 517

MySQL - Insert data in one colum, preserving others, with ON DUPLICATE KEY UPDATE

I have a table with lets say 4 fields.

table: id(autoincremental), col1, col2, col3

There are several rows with data for id, col1 and col2.

Col3 is empty.

I would like to fill col3 with values, in the existing rows with one query like this:

INSERT INTO table(id, col3)
VALUES
(1, 'value1'),
(2, 'value2'),
(3, 'value3'),
...
ON DUPLICATE KEY UPDATE
id = VALUES(id),
col3 = VALUES(col3);

But I get an error because col1 and col2 don't have a default value. I just want to update col3, and preserve other column values. How can I do?

Upvotes: 1

Views: 63

Answers (3)

Sirmyself
Sirmyself

Reputation: 1564

You can update like this : 

ON DUPLICATE KEY UPDATE
    col3 = VALUES(col3);

This will keep the current values for the fields that are not in the update statement.

I read the question again and it seems like your insert would need col1 and col2 as well. You said that they don't have a default value, so I would either

  • add a default value for each
  • mark them as nullable
  • make these parameters mandatory in your application.

So you can't change your table structure my final query would be the following :

INSERT INTO table(id, col1, col2, col3) VALUES
    (1, '', '', 'value1'),
    (2, '', '', 'value2'),
    (3, '', '', 'value3')
    --  ...
ON DUPLICATE KEY UPDATE
    col1 = col1,
    col2 = col2,
    col3 = VALUES(col3);

When a field does not have a default value and is not nullable, the insert query must include a value for that field. So your problem is not in the update part of your query, it is in the insert part.

Upvotes: 0

oxk4r
oxk4r

Reputation: 517

I believe that @Lukasz Szozda comment is the best approach for this issue. So I'll choose it as a solution. Thanks.

Mark col1, col2 as nullable.

Upvotes: 0

sagi
sagi

Reputation: 40481

You can use a simple update statement :

Update yourtable t
Set t.col3 = case when t.id = 1 then ‘value1’
                   case when t.id = 2 then ‘value2’
                    ..... 
                   else t.col3 end;

And you can also filter the desired IDs for better performance

Upvotes: 1

Related Questions