Firstly, I can will need to run this statement in mysql or access db, either is fine by me as I can do it at source in the access db or in the mysql db I export to.
Basically I want an update statement which adds a sequential number to a table for a select statement. I dont mind if every time I run it that it populates every row again. It does not need to find the last sequence number.
Dont worry about any of the table names etc below being reserved words, ive just done as an example to try and keep as simple as possible.
Ive made these names up so that it makes more sense.
Table name is called TABLEA
Within TABLEA are NAME, UNIQUENUMBER, DATE, SEQUENCE
Basically my primary key so to speak is on NAME, UNIQUENUMBER
What I want to do is write an update statement which populates the SEQUENCE column with a sequential number for the NAME but the sequence must then restart for the next NAME.
I.E My select would be
Select NAME, UNIQUENUMBER
Order by NAME, UNIQUENUMBER desc
Lets say my data from the select is
I want the update statement to populate the SEQUENCE so that my data would now be
NAME UNIQUENUMBER SEQUENCE
PETER 007 1
PETER 004 2
PETER 001 3
JOHN 101 1
JOHN 099 2
GARY 078 1
GARY 064 2
GARY 045 3
GARY 038 4
I need to achieve this as I then need to run some scripts based on the SEQUENCE for each NAME.
Thanks for your help