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

Answer 1

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 

Answer 2

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.