Update statement to update multiple rows

Question

I have a question regarding the following syntax. Is there a cleaner way to roll this up into one statement rather than two. I've tried several iterations but this seems to be the only way I can successfully execute these two statements.

UPDATE employee
SET hire_date = '1979-03-15'
WHERE emp_id = 'PMA42628M' 

UPDATE employee
SET hire_date = '1988-12-22'
where emp_id = 'PSA89086M';

I tried this as well and I also tried using an AND statement. Neither worked. Basically I am looking for a less newbie way then the method above, if one exists. I spent a long time searching and did not find one.

UPDATE employee
SET hire_date = ('1979-03-15', '1988-12-22')
WHERE emp_id = ('PMA42628M', 'PSA89086M');

Appriciate any advice on this one, and by the way, I am using sql server. Thanks

score 51 · Answer 1 · edited Jun 07 '13 at 16:26

51

Try this one, this will combine multiple selects and returns them as if they come from the DB:

UPDATE e
SET hire_date = t.hire_date
FROM dbo.employee e
JOIN (
    SELECT emp_id = 'PMA42628M', hire_date = '1979-03-15'
    UNION ALL
    SELECT emp_id = 'PSA89086M', hire_date = '1988-12-22'
) t ON t.emp_id = e.emp_id

If you are using SQL Server 2008 or later version, you could also use a different syntax for the derived table:

UPDATE e
SET hire_date = t.hire_date
FROM dbo.employee e
JOIN (
    VALUES
        ('PMA42628M', '1979-03-15'),
        ('PSA89086M', '1988-12-22')
) t (emp_id, hire_date) ON t.emp_id = e.emp_id

edited Jun 07 '13 at 16:26

Andriy M

76,112
17
94
154

answered Jun 05 '13 at 06:01

Devart

119,203
23
166
186

isn't the keyword `AS` missing twice? – Zim84 Jun 05 '13 at 06:22
1

I don't use `AS` keyword in this situation, because it's unnecessary. – Devart Jun 05 '13 at 06:24
The keyword `AS` is not required, but encouraged for better readability. – TT. Jun 05 '13 at 06:53
3

@TT, this is a personal preference. :) – Devart Jun 05 '13 at 07:05
1

@TT.: ...but may be discouraged by those who switch between Oracle and non-Oracle often. (Oracle doesn't support `AS` before table alias declarations: [this works](http://sqlfiddle.com/#!4/d41d8/12053), [this doesn't](http://sqlfiddle.com/#!4/d41d8/12054).) – Andriy M Jun 07 '13 at 16:10
@AndriyM Thanks for the heads up. I just wish that relational DB vendors would implement more of the standard... – TT. Jun 08 '13 at 03:48
what doe the `dbo` do in the FROM clause? – Nathan Hinchey Mar 11 '19 at 18:36
@NathanHinchey nothing. dbo is just the default schema of a database and since OP didn't give an own schema I guess Devart just assumed that the table is located on the default schema. – Manuel Zelenka Aug 02 '19 at 09:58
it worked! Thank you! I never came across using FROM clause with UPDATE one! – Gaurav Gupta May 19 '20 at 08:54
I prefer this solution!! – KY Lu Aug 09 '20 at 14:27

score 15 · Answer 2 · answered Jun 05 '13 at 06:24

15

I am looking for a less newbie way

Doing two separate update statements is (according to me) "the less newbie way" you could complicate stuff and do something like this.

update employee
set hire_date = case emp_id
                  when 'PMA42628M' then '1979-03-15'
                  when 'PSA89086M' then '1988-12-22'
                end
where emp_id in ('PMA42628M', 'PSA89086M')

but what would that gain you? The entire update would run in one implicit transaction so if you want your two updates to be in a transaction you just use begin transaction .... commit.

answered Jun 05 '13 at 06:24

Mikael Eriksson

136,425
22
210
281

4

I ended up on this question because I was googling for a way to optimize a slow (interactive) application that does about 100K update statements over the course of a minute. Switching to a combined variant like suggested gave us about 700% speedup so we now do the same work in less than ten seconds. Putting it all in a transcation doesn't help because it's the dispatch and roundtrip time for all the SQL statements that takes up the majority of the time. – flodin Feb 07 '14 at 11:00
In my query my `WHERE id IN (...)` is gnarly, so this is a much neater way than two `UPDATE`s. – BrianHenryIE Jun 01 '23 at 21:47

TT. · Answer 3 · 2018-12-14T04:44:39.957

You can make a temporary table or a table variable containing the updates you want to do, then run the UPDATE statement linking the table to the table you intend to update.

Note that for two updates, you get two statements: the INSERT into the update table and the UPDATE statement itself. The number of statements remains two though for as many updates you need to do.

CREATE TABLE #employee (emp_id VARCHAR(9) NOT NULL PRIMARY KEY,hire_date DATE NOT NULL);
INSERT INTO #employee (emp_id,hire_date)
VALUES ('PMA42628M','2013-06-05'),('PSA89086M','2013-06-05');

CREATE TABLE #target_updates(emp_id VARCHAR(9) NOT NULL,hire_date DATE NOT NULL);
INSERT INTO #target_updates (emp_id,hire_date)
VALUES ('PMA42628M','1979-03-15'),('PSA89086M','1988-12-22');

UPDATE
    #employee
SET
    hire_date=tu.hire_date
FROM
    #employee AS e
    INNER JOIN #target_updates AS tu ON
        tu.emp_id=e.emp_id;

SELECT
    *
FROM
    #employee
ORDER BY
    emp_id;

DROP TABLE #target_updates;
DROP TABLE #employee;

This shd be the accepted answer as its applicable to updating n number of rows from a table that already exists as well — Rashmi Pandit, Oct 17 '16 at 01:13

score -2 · Answer 4 · answered Jul 13 '18 at 13:18

-2

update table_name set='value' where orgid in (idnum1, idnum2)

answered Jul 13 '18 at 13:18

A.Goutam

3,422
9
42
90

Update statement to update multiple rows

4 Answers4

Linked

Related