Update operation with Array in Sql management studio
I am new to SQL and want to know if an array can be used to update records in a table.
Array elements refer to columns in a table. for example Item [0] refers to column1, item [1] refers to column2, and item [2] refers to column3. Item [4] again refers to column1, and so on.
I want to match item [0] with the values in column1 to identify a specific record and update column 2 and 3 with the values of Item [3] and [4].
Based on array insertion, I wrote the following query.
Update dbo.pv_service
Set pv_services.Service=@S and DsCode=@d
((22,'TEST',10),(0,'TES2',10) as (@P,@S,@d)) as T
where T.@P=pv_services.ServiceCode
Is it possible?
Hello,
+3
source to share
1 answer
As mentioned in the comment, you can do this with a temporary table:
CREATE TABLE #array(
Col1 INT,
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10)
)
/*
optional TO DO here: create index on #array table if necessary
*/
INSERT INTO #array
VALUES (1,'some','randrom','text'),
(1345,'any','other','bullsh**')
UPDATE YT
SET YT.Col2 = A.Col2,
YT.Col3 = A.Col3,
YT.Col4 = A.Col4
FROM dbo.YourTable YT
INNER JOIN #array A ON YT.Col1 = A.Col1
DROP TABLE #array
or you use the variable table:
DECLARE @array TABLE
(
Col1 INT,
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10)
)
INSERT INTO @array
VALUES (1,'some','randrom','text'),
(1345,'any','other','bullsh**')
UPDATE YT
SET YT.Col2 = A.Col2,
YT.Col3 = A.Col3,
YT.Col4 = A.Col4
FROM dbo.YourTable YT
INNER JOIN @array A ON YT.Col1 = A.Col1
+1
source to share