CODEWITHSUNDEEP
sqlsql-server
user11797522
user11797522

Reputation:

Update multiple values in SQL

I'm looking to do something like this 

Update Tenancy
set Text5 = ('55', '57', '89')
where PK in ('1', '2', '3')

I'm doing this with 1000 PKs, so just looking for a fast and safe way.

This is the output I'm looking for:

pk      text5
--------------
1       55
2       57
3       89

Upvotes: 1

Views: 587

Answers (4)

SQLpro
SQLpro

Reputation: 5113

Use a table value constructor like this :

WITH 
T AS (SELECT * FROM (VALUES ('1', '55'),
                            ('2', '57'),
                            ('3', '89')
                    ) AS TBL (pk, text5)
      ) 
UPDATE TNC
SET    Text5 = T.text5
FROM   Tenancy AS TNC
       JOIN T ON TNC.PK = T.PK;

Beware… this constructor is limited to exactly 1000 tuples !

Upvotes: 3

Keith
Keith

Reputation: 1038

Another option is creating a temp table, to join to:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
GO

CREATE TABLE #TempTable
(
    [Id] INT,
    [Text5] INT
)

INSERT INTO #TempTable
SELECT 1,55 UNION ALL
SELECT 2,57 UNION ALL
SELECT 3,89;

UPDATE T
SET T.Text5 = TT.Text5
FROM Tenancy AS T
INNER JOIN #TempTable AS TT ON T.[PK] = TT.[Id];

DROP TABLE #TempTable;

One advantage of this is, if the data is in Excel (or similar) you can use a formula to create your SELECT statements to populate the temp table. You can also index the temp table, should it require it.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can update the text5 with values constructor :

update t 
     set t.text5 = tt.text5
from Tenancy t cross apply
     ( values (1, 55), (2, 57), (3, 89) 
     ) tt(pk, text5)
where tt.pk = t.pk;

Upvotes: 0

JNevill
JNevill

Reputation: 50019

A CASE Expression will work for you:

Update Tenancy
set Text5 = CASE WHEN PK = '1' THEN '55' WHEN PK = '2' THEN '57' WHEN PK = '3' THEN '89' END
where PK in ('1','2','3')

This will get tedious though, so a table holding this relationship between pk and text5 would be a better solution in the end.

Upvotes: 0

Related Questions