Update statement with Array in Sql management studio

Question

I am new to SQL and wanted to know is it possible to use an array to update records in table.

The elements of the array relates to columns in the table. e.g. Element[0] relates to column1, Element[1] relates to column2 and Element[2] relates to column3. Element[4] relates to column1 again and so on.

I want to match element[0] to values in column1 to identify a particular record and update column 2 and 3 with values of Element[3] and [4].

Based on insert into with an array I wrote the below 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 this even possible?

Regards,

Answer 1

As mentioned in comment you can do it 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 a table-variable:

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