Sorry: Trying to understand this piece of code

[Vincent Jones]

I have this piece of script and I'm trying to understand what the
'SET t.Z_ASSOC_ROW = (t.rowid - m.MinRowID) + 1'
and the 'INNER JOIN' line is doing
UPDATE t
SET t.Z_ASSOC_ROW = (t.rowid - m.MinRowID) + 1
FROM tassoc t
INNER JOIN (SELECT Booking,MIN(rowid) AS 'MinRowID'
FROM tassoc
GROUP BY Booking) m
ON m.Booking = t.Booking

table 'tassoc'
rowid booking z_assoc_row
1 38 0
2 40 0
3 41 0
4 42 0
5 43 0
6 44 0
7 53 0
8 53 0
9 102 0
10 103 0
11 103 0

[David Portas]

It might be clearer if you convert this proprietary code into equivalent
Standard SQL:

UPDATE Tassoc
SET z_assoc_row = rowid + 1
-
(SELECT MIN(rowid)
FROM Tassoc AS M
WHERE booking = Tassoc.booking)

This enumerates multiple rows for each booking by setting z_assoc_row = 1
for the first row for the booking and incrementing on subsequent rows. The
sequence is based on Rowid. If the Rowid's for a booking are not consecutive
then the sequence will have gaps.

--
David Portas
SQL Server MVP
--