CODEWITHSUNDEEP
mysql
Intact Abode
Intact Abode

Reputation: 392

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

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

Upvotes: 1

Views: 8187

Answers (5)

Atul Kamani
Atul Kamani

Reputation: 105

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.

Upvotes: 4

witchi
witchi

Reputation: 401

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;

Upvotes: -1

Iok_the_best
Iok_the_best

Reputation: 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.

Upvotes: -1

Tuncay Göncüoğlu
Tuncay Göncüoğlu

Reputation: 1767

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 0

Related Questions