Inserting/Updating rows with some columns and preserve values of others

Question

A little background information: I have a table called table_a, which has 12 columns. I want to insert or update rows with values for 6 of these columns, while I don't wanna lose the data in the other 6 columns. And I wanna do this with a parameterized query in C#.

field1 is Unique.

> SELECT * FROM table_a;

+----+--------+--------+---+---------+---------+
| Id | field1*| field2 |...|field11 | field12 |
+----+--------+--------+---+---------+---------+
|  1 | AA     | BB     |...| KK      | LL      |
|  2 | AA     | BB     |...| KK      | LL      |
|  3 | AA     | BB     |...| KK      | LL      |
|  4 | AA     | BB     |...| KK      | LL      |
+----+--------+--------+---+---------+---------+

The Problem is, my first thought was to use REPLACE INTO, unfortunately this will delete the 6 not touched values:

> REPLACE INTO table_a (field1, ..., field6) VALUES ('AA', ...);
> REPLACE INTO table_a (field1, ..., field6) VALUES ('AB', ...);

+----+--------+--------+---+---------+---------+
| Id | field1*| field2 |...| field11 | field12 |
+----+--------+--------+---+---------+---------+
|  1 | AA     | BB     |...| NULL    | NULL    |
|  2 | AB     | BB     |...| NULL    | NULL    |
|  3 | AC     | BB     |...| KK      | LL      |
|  4 | AD     | BB     |...| KK      | LL      |
+----+--------+--------+---+---------+---------+

My second thought was to use INSERT INTO ... ON DUPLICATE KEY UPDATE, but then I'd have to bind the parameters a second time, the first time in the INSERT part and the second time in the UPDATE part, like this:

INSERT INTO table_a (field1, ..., field6)
VALUES(?, ..., ?)
ON DUPLICATE KEY UPDATE
field1 = ?, ..., field6 = ?;

That would preserve my data, but I have to bind the parameters twice.

The third option would be to create another two queries and use the SELECT and INSERT INTO/UPDATE pattern.

So, my question is, how do I do this the smart way?

Answer 1

I think you've listed all the available options, along with the pros/cons of each. As for the third option, you would probably want to wrap your two queries in a transaction to ensure that the operation remains atomic.

Answer 2

Your second option sounds like a winner for single row updates.

Your third option is good if you insert/update many rows at once (as it will not matter much that you have two queries then - providing each does only what it is supposed to do).

UPDATE:
Digging through documentation one finds that you can bind once if you wish - you can refer to the originally bound values with VALUES()

UPDATE2: Well, actually you can not get to the bound values with VALUES(column), so instead two suggestions that actually might help:

• did you check about using named parameters (then you would not need to bound them twice)?
• did you consider stored procedures?

Answer 3

Hi let's say you want to modify field2 to field6.

why wouldn't you do:

replace into table_a select field1,new_value2,...,new_value6,field7,...,field12 from table_a where field1=filter_field1;

You put the new values and you get the others value by querying the table you're updating.