Use Query Result as Table Name for Update-CodePudding

I have a table which returns rows which I want to use for an update query.

This query:

SELECT 
   'data_'  REPLACE(ft.uid, '-', '_') dataview_table,  
    ft.id
FROM  father_table ft

returns:

dataview_table                              id
data_A65225EB_AEE4_478B_AC86_03E5F0D92F5B   10000291
data_0234FF89_E561_4918_870A_3FFD9928E647   10000291
data_0234FF89_E561_4918_870A_3FFD9928E647   10000291
data_021F9D95_8F87_402B_A53B_5C5AE4084D9C   10000291

What I want to do is to use dataview_table and id as a condition on my update query, so for each row on the father_table it will perform this:

UPDATE dataview_table SET status = 5 WHERE id = id

How can I do that?

CodePudding user response：

There is no way you can directly fetch and execute it, instead, you'll have to use the Dynamic SQL or Query String approach. Like this

DECLARE @Qry VARCHAR(MAX)

;WITH CTE
AS
(
    SELECT 
        Qry = 'UPDATE data_'  REPLACE(ft.uid, '-', '_') ' SET status = 5 WHERE id = '     CAST(ft.id AS VARCHAR(20))
        FROM  father_table ft
)
SELECT
    @Qry = STRING_AGG(Qry,';')
    FROM CTE
    
EXEC(@Qry)