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