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