SQLTeam.com | Weblogs | Forums

Update multiple records with select BUT


#1

I have a table with a KeyID that I want to update using a select statement. Sounds standard. But, the Select statement can collect multiple records of the same KeyID. In other words one record of that table has to be updated several times in order to be fully updated.

Table: Week (to keep it simple I only mention 3 days, but I have the full week here)

KEYID    WeekNr    MONDAY     TUESDAY    WEDNESDAY
  1        3        free        free      NULL
  2        4        NULL        NULL      free

Update Week set MONDAY = CASE WHEN ord = 1 THEN NULL ELSE W.MONDAY END,
            set TUESDAY = CASE WHEN ord = 2 THEN NULL ELSE W.TUESDAY END
            set WEDNESDAY = CASE WHEN ord = 3 THEN NULL ELSE W.WEDNESDAY END
FROM Week W
inner join (SELECT TOP (@Top) * from (
Select 1 as ord, * from Week where Monday = 'free'
UNION ALL
Select 2 as ord, * from Week where Tuesday = 'free'
UNION ALL
Select 3 as ord, * from Week where WEDNESDAY = 'free')a
order by WeekNr, ord)x
on W.KeyID = x.KeyID

I want to update the first @Top days that have the reference 'free' in MONDAY, TUESDAY or WEDNESDAY. If I do it the way I posted, only the Monday is updated for @TOP = 2. If @TOP = 3 Then MONDAY and WEDNESDAY is updated, BUT NOT TUESDAY as it is the second entry of Table X having the same KEYID

To make it easier to understand this is what table x (that is inner joined) looks like:

ord    KeyID   WeekNr
1       1        3
2       1        3
3       2        4

Now that I'm thinking about it, it seems to me that this can only be achieved iteratively


#2

What if you have 1,000,000 rows? If @Top = 3 you'll only get the first three rows and all will be ord=1

Could you post your desired output given the input table you already posted?


#3

For @TOP = 1 the desired output would be

KEYID    WeekNr    MONDAY     TUESDAY    WEDNESDAY
  1        3        NULL        free      NULL
  2        4        NULL        NULL      free

For @TOP = 2 the desired output would be

KEYID    WeekNr    MONDAY     TUESDAY    WEDNESDAY
  1        3        NULL        NULL      NULL
  2        4        NULL        NULL      free

For @TOP = 3 the desired output would be

KEYID    WeekNr    MONDAY     TUESDAY    WEDNESDAY
  1        3        NULL        NULL      NULL
  2        4        NULL        NULL      NULL

I want to change the first n appearances of 'free' in any of the weekday columns to NULL with n = @Top
With a 1'000'000 row nothing changes as I update starting from the Top and then the first @Top appearances of 'free'


#4

Something like this might do it:

update week
set Monday  = IIF(rn = 1 and Monday = 'free', NULL, Monday),
    Tuesday = IIF(rn = 2 and Tuesday = 'free', NULL, Tuesday)
...etc...    
from (
select rn = row_number() over(partition by weeknr)
from week
where 'free' in (Monday, Tuesday, ... etc)
) week
where rn <= @top