Consider that i have 2 tables like as bellow :
The first table contains the table that i want to update with the right columns
create table tbl_forUpdate(
id int primary key identity,
tableName varchar(50),
columnName varchar(100))
insert into tbl_forUpdate values('tbl_Order','order_status,order_type')
insert into tbl_forUpdate values('tbl_OrderDetail','orderDetails_status')
The second table contains the rows need to updated with the value
create table tbl_OrderToUpdate(
id_order int,
id_orderDetails int
)
insert into tbl_OrderToUpdate values(4558,2)
insert into tbl_OrderToUpdate values(7894,1)
the idea is create an update query for each table in "tbl_forUpdate" with the column that i have
In my case it will be two Update query one for tbl_order with column order_status and order_type
and the second for tbl_OrderDetails with column orderDetails_status
The value of the update it will be -1 for the row that exists in tbl_OrderToUpdate
Any idea how can i do that ?
Thanks for help !
August 3, 2022 at 6:10 pm
Is this the output you want? I wrote a script to generate it, but it is ugly because I assumed the ID columns in the tables to be updated have the same names as the columns in the tbl_OrderToUpdate table and I didn't want to hard code them. If the ID columns are named [ID] the script would be far simpler. If this is not the desired output, please provide it.
UPDATE dbo.tbl_Order SET order_status = -1, order_type = -1
WHERE id_order IN (4558,7894)
UPDATE dbo.tbl_OrderDetail SET orderDetails_status = -1
WHERE id_orderDetails IN (2,1)
August 4, 2022 at 7:41 am
Hello,
YEs the output that you did is correct this what i want
Thanks
This is pretty ugly. I didn't want to hard code the column names so used a dynamic sql output variable to get the list of ids to be updated. If the table name and column name were listed as columns in the second table it would be easier.
DECLARE @Debug INT = 1,
@TableName VARCHAR(100) ,
@ColumnName VARCHAR(100),
@UpdateString VARCHAR(100),
@IDList VARCHAR(1000),
@Counter INT = 1,
@MaxCounter INT,
@SQL NVARCHAR(1000),
@SQL2 NVARCHAR(1000),
@ID1 VARCHAR(20),
@ID2 VARCHAR(20),
@InSQL NVARCHAR(1000)
-- Get the column names from tbl_OrderToUpdate
SELECT @ID1 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_OrderToUpdate' AND ORDINAL_POSITION = 1
SELECT @ID2 = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_OrderToUpdate' AND ORDINAL_POSITION = 2
SET @MaxCounter = (SELECT COUNT(*) FROM DBO.tbl_forUpdate)
WHILE @Counter <= @MaxCounter
BEGIN
-- Get table name, add comma to columnnname so it can be replaced with the update value
SELECT @TableName = tableName,
@UpdateString = CONCAT(columnName, ',')
FROM dbo.tbl_forUpdate
WHERE id = @Counter
--Generate the update values
SET @UpdateString = TRIM( ', ' FROM REPLACE(@UpdateString, ',', ' = -1, '))
SET @ColumnName = CASE
WHEN @TableName = 'tbl_Order' THEN @ID1
WHEN @TableName = 'tbl_OrderDetail' THEN @ID2
ELSE ''
END
-- Get the list of ids to be updated
SET @SQL2 = CONCAT('SELECT @inlistout_out = STRING_AGG(', @ColumnName,','','') FROM dbo.tbl_OrderToUpdate')
EXEC SP_EXECUTESQL @SQL2, N'@inlistout_out VARCHAR(1000) OUT', @IDList OUT;
-- Generate the final update
SET @SQL = CONCAT(
'UPDATE dbo.', @TableName, ' SET ', @UpdateString, CHAR(13),
'WHERE ', @ColumnName, ' IN (', @IDList, ')'
)
-- Return the update sql
RAISERROR(@SQL, 10,1) WITH NOWAIT
-- Execute the update sql if @debug = 0
IF @Debug = 0
BEGIN
EXEC SP_EXECUTESQL @Query = @SQL
END
SET @Counter += 1
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply