How to update records with same ID in same table in MysQL

Question

I have a table Table1 which has data like

-------------------------------
code     value
-------------------------------
02      null
05      null
06      hi
02      hello
05      how

I want to update the same table as

-------------------------------
id     value
-------------------------------
02      hello
05      how
06      hi
02      hello
05     how

I used the following query, but is not working:

update Table1 set value=value where id is null

Possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — Jens, Aug 03 '17 at 11:39
Note that you appear to have no PRIMARY KEY, which may well prove to be problematic in due course! — Strawberry, Aug 03 '17 at 12:14

score 4 · Accepted Answer · edited Aug 03 '17 at 12:36

4

You need to create alias of the Table1 and than set value into null record as below:

UPDATE Table1 t1, (
  SELECT DISTINCT id, value 
  FROM Table1 
  WHERE value IS NOT NULL AND value != ''
) t2 SET t1.value = t2.value 
WHERE t1.id = t2.id;

Above query is tested and working.

edited Aug 03 '17 at 12:36

GuRu

1,880
3
23
32

answered Aug 03 '17 at 11:47

Atul Kamani

105
6

score 0 · Answer 2 · answered Aug 03 '17 at 11:40

You need a join to connect the two tables (correlated subqueries are tricky in MySQL update statements):

update table1 t1 join
       (select id, min(value) as value
        from table1
        group by id
       ) tt1
       on t1.id = tt.id
    set t1.value = tt1.value
    where t1.value is null;

score 0 · Answer 3 · answered Aug 03 '17 at 11:48

0

I think there was a simpler syntax:

update t1 from table1 t1, table2 t2 set t1.value=t2.value where t1.code=t2.id

Tho I havent tested it right now.

answered Aug 03 '17 at 11:48

Tuncay Göncüoğlu

1,699
17
21

For this specific example, this may well be correct, but I don't think this solution scales up to a more general answer. – Strawberry Aug 03 '17 at 11:58

score -1 · Answer 4 · answered Aug 03 '17 at 11:52

-1

Query should be written as:

update Table1 set value=' ' where id = 1.

Here value is the column name and you like to update some content(hi, hello etc) to it. If the content is String, then enclose it with single/double quotes. For what row you want to update that comes after WHERE clause. Here you are setting condition as WHERE id = 1. So for id =1, the value gets updated.

answered Aug 03 '17 at 11:52

Iok_the_best

1
1

It looks like the question may be worded incorrectly for the results they're after. The only difference between before and after I can see is that the 'code' column has become an 'id' column. Instead of an update I think the user is after an alter table statement. – Paul Aug 03 '17 at 12:02

score -1 · Answer 5 · answered Aug 03 '17 at 11:53

-1

Generate a script with a SELECT statement and execute it (don't forget commit at the end):

select concat('update Table1 set value=`', value, '` where id =', id, ';') as script from Table1 where value is not null;

answered Aug 03 '17 at 11:53

witchi

345
3
16

How to update records with same ID in same table in MysQL

5 Answers5