CODEWITHSUNDEEP
mysql
Gopi
Gopi

Reputation: 5877

Update Table - Mysql

enter image description here

Need to update column c2 for all the values of same c3. i.e c3 has five 0's and top three 0's has 10 in c2. Here I need to update record 7 and 9 with value 10. At the end all 0's in c3 should have same c2 value i.e 10

Upvotes: 2

Views: 109

Answers (2)

Germann Arlington
Germann Arlington

Reputation: 3353

How do you want to decide which record to take the value from for updating others?
select c3 from yourTable group by c3 will return your distinct c3 values, now you can get corresponding c2 values fro each of c3, but how do you want to decide which value to use to update others?

Edit:
Few SQL statements approach:
set @uniqueC3s = (select c3 from yourTable group by c3);
-- loop through the resutlset set @requiredC2Value = (select TOP 1 c2 from yourTable where c3 = @uniqueC3s order by c1);
update yourTable set c2 = @requiredC2Value where c3 = @uniqueC3s;
-- end of loop

Upvotes: 0

Omesh
Omesh

Reputation: 29081

You need to take self join and then update the column C2 as:

UPDATE table_name a
       INNER JOIN table_name b
           ON a.C2 = b.C3
SET a.C2 = b.C2
WHERE b.C2 <> 0;

Upvotes: 1

Related Questions