How can I change the data in only one cell of a mysql table. I have problem with UPDATE because it makes all the parameters in a column change but I want only one changed. How?
1
-
What have you tried so far? Can you share the query you are using? Usually, you do not update "all the parameters in a column"Nico Haase– Nico Haase2020-06-01 11:57:51 +00:00Commented Jun 1, 2020 at 11:57
Add a comment
|
8 Answers
You probably need to specify which rows you want to update...
UPDATE
mytable
SET
column1 = value1,
column2 = value2
WHERE
key_value = some_value;
7 Comments
ki9
I was confused by this answer, thinking SET selected rows to change, and WHERE changed them.
weefwefwqg3
can the condition in WHERE be column1 = old_value ?
Brian Hooper
@weefwefwqg3, yes, it can, but there is no point. If you aren't changing the value, just leave it out of the SET part.
weefwefwqg3
@Brian Hooper: I do need to change the value, I mean to ask whether I can do this:
UPDATE mytable SET column1 = new_value WHERE column1 = old_value; ??Brian Hooper
@weefwefwqg3, Ah, I see, sorry, an attack of myopia. Yes, that would be perfectly fine.
|
My answer is repeating what others have said before, but I thought I'd add an example, using MySQL, only because the previous answers were a little bit cryptic to me.
The general form of the command you need to use to update a single row's column:
UPDATE my_table SET my_column='new value' WHERE something='some value';
And here's an example.
BEFORE
mysql> select aet,port from ae;
+------------+-------+
| aet | port |
+------------+-------+
| DCM4CHEE01 | 11112 |
| CDRECORD | 10104 |
+------------+-------+
2 rows in set (0.00 sec)
MAKING THE CHANGE
mysql> update ae set port='10105' where aet='CDRECORD';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
AFTER
mysql> select aet,port from ae;
+------------+-------+
| aet | port |
+------------+-------+
| DCM4CHEE01 | 11112 |
| CDRECORD | 10105 |
+------------+-------+
2 rows in set (0.00 sec)
2 Comments
Mohammed
Thanks for this demonstration. The above query will update all the instances of CDRECORD in your column aet. However, when updating a specific cell in a specific column, it is better to do that based on the ID of the column, i.e., where ID=xx
Pliny
It is usually best to use the rows primary key, the rows unique identifier (which is often the ID, but not always). Honestly it depends on what you want to do which defines the "best" practice
UPDATE will change only the columns you specifically list.
UPDATE some_table
SET field1='Value 1'
WHERE primary_key = 7;
The WHERE clause limits which rows are updated. Generally you'd use this to identify your table's primary key (or ID) value, so that you're updating only one row.
The SET clause tells MySQL which columns to update. You can list as many or as few columns as you'd like. Any that you do not list will not get updated.
Comments
UPDATE only changes the values you specify:
UPDATE table SET cell='new_value' WHERE whatever='somevalue'
Comments
Try the following:
UPDATE TableName SET ValueName=@parameterName WHERE
IdName=@ParameterIdName
1 Comment
Nico Haase
Please add some explanation to your answer such that others can learn from it - what does that
@parameterName do?UPDATE TABLE <tablename> SET <COLUMN=VALUE> WHERE <CONDITION>
Example:
UPDATE TABLE teacher SET teacher_name='NSP' WHERE teacher_id='1'
Comments
try this.
UPDATE `database_name`.`table_name` SET `column_name`='value' WHERE `id`='1';
1 Comment
Toby Speight
Welcome to Stack Overflow! Whilst this code snippet is welcome, and may provide some help, it would be greatly improved if it included an explanation of how it addresses the question. Without that, your answer has much less educational value - remember that you are answering the question for readers in the future, not just the person asking now! Please edit your answer to add explanation, and give an indication of what limitations and assumptions apply.
Some of the columns in MySQL have an "on update" clause, see:
mysql> SHOW COLUMNS FROM your_table_name;
I'm not sure how to update this but will post an edit when I find out.
1 Comment
Jake_Howard
If there is an "on update" it will be in the "Extra" column of the table you get when executing the above command.