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

1
votes

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?

3
mysql
Mark col1, col2 as nullable – Lukasz Szozda
Wait , are you trying to update the table by inserting into it? – sagi
You only have to update the values you want to change. ID is supposedly the same (isn't this a duplicate key?) so it makes no sense to update it to itself. You are not having a problem with the update part, you are having a problem with the insert part, because you are not providing values for fields that are required. – Havenard
@havenard isn't that my answer in a nutshell? – Sirmyself
@Lukasz Szozda, I picked your comment as solution, cause I believe is the simplest and most efective way of address this issue. Regards. – oxk4r

3 Answers

1
votes

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

0
votes

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.

0
votes

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.