CODEWITHSUNDEEP
sql
Steven Rogers
Steven Rogers

Reputation: 1994

Update a single column on multiple rows with one SQL query

I need to update a single column over a thousand rows in the database. Normally when I need to do this, I'll do the following:

UPDATE [table] SET DATA="FOO" WHERE ID=23;
UPDATE [table] SET DATA="ASD" WHERE ID=47;
UPDATE [table] SET DATA="FGH" WHERE ID=83;
UPDATE [table] SET DATA="JKL" WHERE ID=88;
UPDATE [table] SET DATA="QWE" WHERE ID=92;
UPDATE [table] SET DATA="BAR" WHERE ID=97;

I feel like there should be a way to do this easily, but after searching around online, I cannot find a solution.

What I was hoping for was this:

-- THIS DOES NOT WORK
UPDATE [table]
  SET DATA=("FOO", "ASD", "FGH", "JKL", "QWE", "BAR") 
  WHERE ID=(23, 47, 83, 88, 92, 9);

An important piece of information to note is that I am always setting one column, the same column across a specific set of rows.

Does anyone know the best way to do this?

Upvotes: 4

Views: 85815

Answers (5)

granadaCoder
granadaCoder

Reputation: 27842

Sql Server

UPDATE t1 
set DATA = derived1.NewValue
From
[table] t1 join
(
Select 'Foo' as NewValue, 23 as MyId 
union all Select 'ASD' as NewValue, 47 as MyId 
union all Select 'FGH' as NewValue, 83 as MyId 
) as derived1
on t1.ID = derived1.MyId

Note, you can hydrate/populate "derived1" anyway you'd like. As long as the result set is the correct rows of "NewValue" and "MyId".

Upvotes: 2

trincot
trincot

Reputation: 350009

You could use the MERGE statement which is in the SQL:2003 standard and available in Transact-SQL since SQL Server 2008:

MERGE mytable
USING (VALUES (23, 'FOD'),
              (47, 'ASD'),
              (83, 'FGH'),
              (88, 'JKL'),
              (92, 'QWE'),
              ( 9, 'BAR')) AS pairs(id2, data2)
     ON id = id2 
WHEN MATCHED 
     THEN UPDATE SET data = data2

The USING clause allows to specify a derived table using a table value constructor (See example under point D on that page).

Alternatively, the more commonly implemented SQL:92 standard syntax to do this would be:

UPDATE mytable
SET    data = 
       CASE id
            WHEN 23 THEN 'FOD'
            WHEN 47 THEN 'ASD'
            WHEN 83 THEN 'FGH'
            WHEN 88 THEN 'JKL'
            WHEN 92 THEN 'QWE'
            WHEN  9 THEN 'BAR'
       END
WHERE  id IN (23, 47, 83, 88, 92, 9);

The obvious downside is that you end up specifying the id values twice. You could do without the WHERE clause and add ELSE data in the CASE construct, but then you would actually update all rows, which is inefficient and may have undesired side-effects (via triggers).

Upvotes: 11

Andrews B Anthony
Andrews B Anthony

Reputation: 1381

You can actually do it using insert into ...on duplicate key update

insert into [table](ID,DATA) 
values(23,'FOO'),(47,'ASD'),(54,'DSF')..,
on duplicate key update DATA=values(DATA)

Upvotes: 3

TheGameiswar
TheGameiswar

Reputation: 28890

one way i could think of is join..The way you are trying to update wont scale

---insert your updates in a table like below every time you want to update data.

create table #test
(
data varchar(2),
id int
)

insert into #test
select 'abc',1
union  all
select 'cde',2

now you update your main table like below

update mt
set mt.data=t.data
from maintable  mt
join
#test t
on t.id=mt.id

Upvotes: 1

JNevill
JNevill

Reputation: 50019

I'm assuming that you are in MySQL. You can use a combination of "Field" and "Elt" functions to do what you need in a single query (beside the CASE WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN END or IF(<condition>,<output>,if(<condition2>,<output2>, if())) methods.

UPDATE [table] SET DATA=ELT(FIELD(ID,
   13, 14, 15, 16, 17, 18, 19),'FOO', 'ASD', 'FGH', 'JKL', 'QWE', 'BAR');

This is similar to the DECODE() function in Oracle, which I wish had counterparts in other DBMS's.

Upvotes: 4

Related Questions