How to Update Specific Columns in MySQL While Preserving Others?

Updating Columns in MySQL

In a situation where you possess a table with multiple columns containing data, you may encounter the need to modify specific columns while preserving the values in others. For instance, consider a scenario with a MySQL table named "table1" comprising three columns: "key_col," "col_a," and "col_b."

Query Premise:

Your goal is to update the values within the "col_a" column, leaving "col_b" intact. The intended outcome is represented by the following query:

INSERT INTO table1 AS t1 (key_col, col_a) VALUES ("k1", "foo"), ("k2", "bar");

Query Issue:

However, upon executing this query, MySQL will likely confront you with an error. The reason for this unsuccessful attempt lies in the utilization of the "INSERT" statement, which is primarily designed for inserting new rows into a table. To achieve the desired result of updating specific table cells, it is imperative to employ the "UPDATE" statement instead.

Solution using UPDATE:

The "UPDATE" syntax follows a precise format:

UPDATE table_name SET column_name = new_value WHERE condition;

Example:

In your case, the suitable queries for updating "col_a" are as follows:

UPDATE table1 SET col_a='k1', col_b='foo' WHERE key_col='1';
UPDATE table1 SET col_a='k2', col_b='bar' WHERE key_col='2';

By executing these queries, you can effectively modify the values in "col_a" while preserving the contents of "col_b" for rows designated by the specified "key_col" values.

The above is the detailed content of How to Update Specific Columns in MySQL While Preserving Others?. For more information, please follow other related articles on the PHP Chinese website!