ka-Pow!!!
Take that! unnecessary table variable! Removed by the power of common table expressions!
I have a table in the form:
| ID | Seller | Description |
| 1 | Alice | widget1 |
| 2 | Alice | widget2 |
| 3 | Bob | widget1 |
| 4 | Bob | widget3 |
| 5 | Bob | widget4 |
But what I really need is a table with an extra column with a sales id for each seller, like this;
| ID | Seller | Sales Id | Description |
| 1 | Alice | 1 | widget1 |
| 2 | Alice | 2 | widget2 |
| 3 | Bob | 1 | widget1 |
| 4 | Bob | 2 | widget3 |
| 5 | Bob | 3 | widget4 |
In a past life I would have written the alter script to add in the column, with an empty field (null or -1); created a temporary table to iterate over every 'Alice' sale and set those values, followed by those for 'Bob'. And this is what you get when you're thinking procedurally. Using Common Table Expressions and a Partition makes this far more efficient (at the cost of more complicated code).
WITH cte (ID, seller, sales_id) AS ( SELECT ID, seller, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) FROM sales WITH (NOLOCK) ) UPDATE S SET S.sales_id = C.Pattern_ID FROM sales S INNER JOIN cte C ON S.ID = C.ID AND S.seller = C.seller
And hey! a legitimate reason to actually use a