All tables have a common column called BranchList. BranchList is a string containing a json array, like this

[ { "Id": 1, "Branch": 259 }, { "Id": 1, "Branch": 253 } ]

I want to remove the json value which is branch 253 from the list by navigating all the tables, It should be like this

[ { "Id": 1, "Branch": 259 } ]

I found a method for a single table like this but it's not enough for me to update all tables.

declare @JSONData nvarchar(max)
declare @Id nvarchar(max)
declare @BranchList nvarchar(max)
declare @Counter INT

select @Counter = count(BranchList) 
from WarehouseTypes 
outer apply OPENJSON(BranchList) as s
where JSON_VALUE(s.value,'$.Branch') = 253;

while (@Counter > 0)
begin
    select @Id = Id, @BranchList = BranchList 
    from WarehouseTypes 
    outer apply OPENJSON(BranchList) as s
    where JSON_VALUE(s.value,'$.Branch') = 253;
   
    set @JSONData = N'' @BranchList '';
    set @JSONData = (select * 
                     from OPENJSON(@JSONData, '$') 
                     with (
                           Id int '$.Id',
                           Branch int '$.Branch'
                          )
                     where not (Branch = 253) 
                     for json path);
   
    exec('update WarehouseTypes set BranchList =''' @JSONData ''' where Id=' @Id);
    set @Counter = @Counter  - 1
end

Thank you in advance for your help.

CodePudding user response：

You may try to generate and execute a dynamic statement. The important step are:

• Get the name of each table containing column BranchList (using the system catalog views).
• Remove the item(s) from the stored JSON array. With current versions of SQL Server you can't delete an item from JSON array, so you need to parse the JSON array as table (using OPENJSON() and explicit schema), filter the rows and output the table's content as JSON (using FOR JSON AUTO).

T-SQL:

DECLARE @stm nvarchar(max)
DECLARE @err int

SELECT @stm = STRING_AGG(
   CONCAT(
      N'UPDATE ',
      QUOTENAME(sch.[name]), '.',
      QUOTENAME(tab.[name]),
      N' SET ', 
      QUOTENAME(col.[name]), ' = (',
      N'SELECT Id, Branch FROM OPENJSON(',
      QUOTENAME(col.[name]),
      N', ''$'') WITH (Id int ''$.Id'', Branch int ''$.Branch'') WHERE Branch <> 253 FOR JSON PATH',
      N')'
   ),
   '; '
)
FROM sys.columns col
JOIN sys.tables tab ON col.object_id = tab.object_id
JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
WHERE col.[name] = 'BranchList' 

PRINT @stm
EXEC @err = sp_executesql @stm
IF @err <> 0 PRINT 'Error found'

The result is a complex statement, containing an UPDATE statement for each table:

UPDATE [dbo].[xxxx] 
SET [BranchList] = (
   SELECT Id, Branch 
   FROM OPENJSON([BranchList], '$') WITH (Id int '$.Id', Branch int '$.Branch') 
   WHERE Branch <> 253 
   FOR JSON AUTO
)