如何从CONCAT输出中自动执行UPDATE语句

Question

我有一个SQL查询，用于像这样用CONCAT构建UPDATE语句。

示例：

select top 10000
concat ('update customer set phone_number = ''',phone_number,
''' where id = ''',id,''';')
from (
select a.phone_number, c.id from customer c
join address a on c.id = a.customer_id
where c.phone_number is null
) as cust_phone;

结果：

update customer set phone_number = '628814232154' where id = '3';
update customer set phone_number = '62896631457' where id = '5';

是否可以使此UPDATE输出自动运行？我还是SQL编程的新手。

Answer 1

为什么要构建字符串？

update c set phone_number = a.phone_number
from customer c
join address a on c.id = a.customer_id
where c.phone_number is null

如果你迫切希望有顶部在那里：

update u set phone_number = t.phone_number
from customer u
join 
(
select top 10000 c.id, a.phone_number 
from customer c join address a on c.id = a.customer_id
where c.phone_number is null
) t 
on u.id = t.id

Answer 2

〔假设sql服务器〕
如果您想采用动态sql路由，那么您发布的代码就快到了。您只需要将查询的输出连接到一个sql字符串中，并使用Exec(@sql(执行它

declare @sql nvarchar(max)
select @sql = @sql + concat ('update customer set phone_number = ''',phone_number,''' 
where id = ''',id,''';',Char(10))
from (
select top 10000 a.phone_number, c.id from customer c
join address a on c.id = a.customer_id
where c.phone_number is null
) as cust_phone;
Exec(@sql)

你会想把你的前n个过滤器移到子查询中，就像我上面做的那样。

Answer 3

try:

UPDATE customer
SET phone_number = (SELECT phone_number
FROM address
WHERE customer.id = address.customer_id)
WHERE phone_number is null;