Updating a column with one query in MYSQL

Question

To update a column in the database i use the following query

UPDATE table_name
SET column1 = value1
WHERE condition;

The problem with this query is that i end up updating one column at a time

i want to update a column by setting a condition and updating a whole column that meets the condition set

An example of what i have tried:

UPDATE adggtnz1.lng01_rpt_animrec
SET origin = 'New'
WHERE origin = NULL;

and the result of this query is

0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0  0

picture of sample data:

Possible duplicate of [Update multiple columns in SQL](https://stackoverflow.com/questions/9079617/update-multiple-columns-in-sql) The answer with most upvotes (not accepted answer) might be what you are looking for. — leo valdez, Jul 30 '18 at 06:39

score 0 · Answer 1 · answered Jul 30 '18 at 06:19

0

use case when clause for conditional update below query be an example for that

UPDATE table_name
SET column1 =  case when 1<2 then value1 else  value2 end 
WHERE condition;

answered Jul 30 '18 at 06:19

@MirieriMogaka yes this query will update only column1 based on condition btw if you share your sample table data and expected updated sample i can help a bit more – Zaynul Abadin Tuhin Jul 30 '18 at 06:26

2SRTVF · Accepted Answer · 2018-07-30T06:55:40.937

NO

WHERE origin = NULL;

YES

WHERE origin is NULL;

create table `lng01_rpt_animrec`
(
  `origin` varchar(10)
)

insert into `lng01_rpt_animrec` (`origin`) values
('EADD'),
(null),
('EADD'),
(null),
(null),
('EADD'),
(null),
('EADD'),
('EADD');

UPDATE
  lng01_rpt_animrec
SET
  origin = 'New'
WHERE
  origin is null;

select * from `lng01_rpt_animrec`

| origin |
| :----- |
| EADD   |
| New    |
| EADD   |
| New    |
| New    |
| EADD   |
| New    |
| EADD   |
| EADD   |

db<>fiddle here

2 Answers2