Plugging in a range of values into two WHERE clauses

Question

I ran into a problem in my job where I mistakenly updated a whole bunch of records. So to revert it back, I followed this question, ordered all the records of the table according to descending order of date of modification, got the second last record, which gave me the value that was prior to my mistaken update. So I wrote something like this to update the record:

update table1 set col1 = (select top 1 col1 from
     (select top 2 * from table1 where col2 = val1 order by dateofmodification desc) X
     order by dateofmodification
where col2 = val1

It works well but here's the problem. I have a whole bunch of records to update, i.e., a whole lot of values to which col2 matches, i.e., val1, val2, val3, ...... I can't put in a range of values because of the two WHERE clauses like so :

update table1 set col1 = (select top 1 col1 from
     (select top 2 * from table1 where col2 in (val1, val2, val3) order by dateofmodification desc) X
     order by dateofmodification
where col2 in (val1,val2, val3)

because how would SQL server know val1 is to be matched to val1 in the two WHERE clauses?

Can anyone help me what to do here?

Edit: Here's a sample table

Employees

name | emp_id | address | department | dateofmodification

I made a mistake and update a bunch of employees' records and messed up their department to some random junk.

So if I run this query

update Employees set department = (select top 1 department from
    (select top 2 * from Employees where address = 'Chicago' order by dateofmodification desc) X
where address = 'Chicago'

it works well, but there are a whole bunch of cities, not just Chicago. I don't want to plug in those values one by one.

To avoid causing unnecessary headaches to SO users, could you show a sample table which demonstrates the problem? — Tim Biegeleisen, Aug 05 '16 at 19:54

Hogan · Accepted Answer · 2016-08-08T15:31:40.887

1

Updates can use tuples -- like this:

update tablename
set (f1, f2, f3, f4) = (select a1, a2, a3, a4 from another_tablename)

If your platform does not support tuples (sql server I'm looking at you) it can be re-written as

update tablename
  set f1=a1, f2=a2, f3=a3, f4=a4
  FROM another_tablename

update based on comment.

No you want this

update Employees

  set department = 
    (select top 1 department from 
        (select top 2 * 
         from Employees 
         where address in ('Chicago', 'New York', 'Dallas', 'Miami') 
         order by dateofmodification desc
        ) X         
     order by dateofmodification asc
    )

see how you need the 2nd order by when doing it this way?

the best way

of course sql server supports fetch and offset as do oracle and db2.

  set department = 
    select department 
    from Employees 
    where address in ('Chicago', 'New York', 'Dallas', 'Miami') 
    order by dateofmodification asc
    offset 1 row
    fetch first 1 row only

cf http://sqlmag.com/sql-server-2012/using-new-offset-and-fetch-next-options

edited Aug 08 '16 at 15:31

answered Aug 05 '16 at 20:08

Hogan

69,564
10
76
117

So if I do `update Employees set department = (select top 1 department from (select top 2 * from Employees where address in ('Chicago', 'New York', 'Dallas', 'Miami') order by dateofmodification desc) X where address in ('Chicago', 'New York', 'Dallas', 'Miami')`, it would update accordingly? – Sidharth Samant Aug 05 '16 at 20:10
@SidharthSamant - you want offset and fetch first -- see my edit. – Hogan Aug 05 '16 at 21:21
1

Thee question is tagged SQL server. That has no support for using tuples in an update statement as shown. – Martin Smith Aug 07 '16 at 16:27
@MartinSmith - you are right they don't even support it with the IN clause like the '92 standard -- https://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors -- updating answer. – Hogan Aug 08 '16 at 15:27

score 0 · Answer 2 · answered Aug 07 '16 at 16:23

There are different ways to get the second most recent modification and that part of your problem was addressed in Hogan's answer. But I think you're also looking for how to run the update across multiple addresses.

What you need to do is correlate the inner query rather than what you tried with the in. This should let you run the whole query just one time rather than repeating it for every value.

update Employees
set department = (
    select top 1 department from (
        select top 2 * from Employees
        where address = Employees.address -- <-- correlated here
        order by dateofmodification desc
    ) e2
    order by dateofmodification
)
where address in ('Chicago', ...)

Plugging in a range of values into two WHERE clauses

2 Answers2