0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Odie
  • 375
  • 4
  • 16

1 Answers1

3

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)
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
  • I'm using `Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64)` and STRING_AGG doesn't seem to work. Is there any alternative for STRING_AGG on your code? – Odie Sep 28 '21 at 10:08
  • You can use XML data type. Check this one https://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator – Jayasurya Satheesh Sep 28 '21 at 10:12