Change One Cell's Data in mysql

Question

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?

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, Jun 01 '20 at 11:57

score 211 · Accepted Answer · edited Jan 09 '19 at 09:51

211

You probably need to specify which rows you want to update...

UPDATE 
    mytable
SET 
    column1 = value1,
    column2 = value2
WHERE 
    key_value = some_value;

edited Jan 09 '19 at 09:51

radbyx

9,352
21
84
127

answered Jun 11 '10 at 16:42

Brian Hooper

21,544
24
88
139

1

I was confused by this answer, thinking SET selected rows to change, and WHERE changed them. – ki9 May 30 '17 at 18:50
can the condition in WHERE be column1 = old_value ? – weefwefwqg3 Jan 24 '18 at 14:09
@weefwefwqg3, yes, it can, but there is no point. If you aren't changing the value, just leave it out of the SET part. – Brian Hooper Jan 24 '18 at 14:21
1

@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;` ?? – weefwefwqg3 Jan 24 '18 at 14:22
2

@weefwefwqg3, Ah, I see, sorry, an attack of myopia. Yes, that would be perfectly fine. – Brian Hooper Jan 24 '18 at 14:26
Would using "WHERE ROW_NUMBER() = 1;" work to update just the first row of the table? (MySQL version 8) – Calab Apr 15 '20 at 09:58
@Calab, probably it would; haven't tried it. But be careful, as "the First Row in the Table" may not be well-defined. – Brian Hooper Apr 24 '20 at 09:13

score 118 · Answer 2 · answered Feb 25 '12 at 04:26

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)

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 — Mohammed, Jan 14 '15 at 17:41
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 — Garret Gang, May 15 '18 at 03:43

score 31 · Answer 3 · answered Jun 11 '10 at 16:41

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.

score 11 · Answer 4 · edited Jun 05 '18 at 11:26

11

UPDATE only changes the values you specify:

UPDATE table SET cell='new_value' WHERE whatever='somevalue'

edited Jun 05 '18 at 11:26

Maarten Wolfsen

1,625
3
19
35

answered Jun 11 '10 at 16:44

gruntled

2,684
19
16

score 9 · Answer 5 · edited Jun 05 '18 at 10:29

9

Try the following:

UPDATE TableName SET ValueName=@parameterName WHERE
IdName=@ParameterIdName

edited Jun 05 '18 at 10:29

Maarten Wolfsen

1,625
3
19
35

answered Jul 22 '14 at 06:02

user3668628

103
2
4

Please add some explanation to your answer such that others can learn from it - what does that `@parameterName` do? – Nico Haase Jun 01 '20 at 11:58

score 7 · Answer 6 · answered Nov 07 '17 at 06:03

7

UPDATE TABLE <tablename> SET <COLUMN=VALUE> WHERE <CONDITION>

Example:

UPDATE TABLE teacher SET teacher_name='NSP' WHERE teacher_id='1'

answered Nov 07 '17 at 06:03

NSP

1,193
4
15
26

score 3 · Answer 7 · edited May 02 '17 at 11:26

3

try this.

UPDATE `database_name`.`table_name` SET `column_name`='value' WHERE `id`='1';

edited May 02 '17 at 11:26

Petter Friberg

21,252
9
60
109

answered May 02 '17 at 07:43

abhay

31
1

1

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](//meta.stackexchange.com/q/114762) 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. – Toby Speight May 02 '17 at 08:54

score 0 · Answer 8 · answered Mar 24 '12 at 22:33

0

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.

answered Mar 24 '12 at 22:33

Jake_Howard

2,533
2
15
13

If there is an "on update" it will be in the "Extra" column of the table you get when executing the above command. – Jake_Howard Mar 24 '12 at 22:34

Change One Cell's Data in mysql

8 Answers8

Linked

Related