Consider a table "users" as below:
id, add_id, add
1, 1, abc
2, null, abc
3, null, xyz
4, 2, xyz
Expected output:
id, add_id, add
1, 1, abc
2, 1, abc
3, 2, xyz
4, 2, xyz
Please suggest a MySQL query to get the desired result.
Add a comment
|
2 Answers
A simple method uses window functions:
select id, max(add_id) over (partition by add), add
from t;
If you want to change the value, then the update would be:
update t join
(select add, max(add_id) as add_id
from t
group by add
) tt
on t.add = tt.add
set t.add_id = tt.add_id
where t.add_id is null;
Comments
You can select the id From the table.
if you have more than 1 row with and aff_id and add, then you must LIMIT the inner SELECT
CREATE TABLE table1 ( `id` INTEGER, `add_id` VARCHAR(4), `add` VARCHAR(3) ); INSERT INTO table1 (`id`, `add_id`, `add`) VALUES ('1', '1', 'abc'), ('2', null, 'abc'), ('3', null, 'xyz'), ('4', '2', 'xyz');
UPDATE table1 t2 SET `add_id` = (SELECT `add_id` FROM (SELECT * FROM table1) t1 WHERE t1. `add` = t2.`add` AND `add_id` IS NOT NULL) WHERE `add_id` IS NULL
SELECT * FROM table1id | add_id | add -: | :----- | :-- 1 | 1 | abc 2 | 1 | abc 3 | 2 | xyz 4 | 2 | xyz
db<>fiddle here
1 Comment
nbk
t2 is the alias of the updated table, as you use the same table for updateing and getting the information, you must use aliases, so that mysql can differentiate them