我有一张桌子:
id location_id company_id
1 1 1
2 2 1
3 3 1
4 4 5
5 5 5
6 6 5
问题是如何将location\u id的所有值更改为一家公司中的最低值,如下所示:
id location_id company_id
1 1 1
2 1 1
3 1 1
4 4 5
5 4 5
6 4 5
编辑:
McNets代码正常工作,但我还有一个问题,这是我编辑的代码:
update t1
set location_id = t2.min_loc
from opinion t1
join (select company_id, min(location_id) min_loc
from opinion
group by company_id) t2
on t2.company_id = t1.company_id
join (select id, house_number, street, city
from location) t3
on t3.id = t1.location_id
where t3.house_number is null and (t3.street is null or t3.street = '') and t3.city is null
它在SQL Server 2016上工作,但我的数据库是MariaDB,我得到一个错误
Syntax error near 'from opinion t1 join (select company_id, min(location_id) min_loc from op' at line 3
有什么建议吗?