Update Query

[Hardeep442]

id               fname               lname            seniority
1                Kaur                Hardeep               1
2               Kaur                Hardeep                2
3                Kaur                Hardeep               3
 4               Kaur                Hardeep                4
5                Kaur                Hardeep               5
6               Kaur                Hardeep                6
7               Kaur                Hardeep                7
8                Kaur                Hardeep               8

suppose i want to delete record # 4, then rest of the records has to be updated
5 should be 4 and so on.. i have my sql statement

UPDATE MemberData SET Seniority = Seniority-1
WHERE (((Seniority)>[]));

i cant figure out what i should put instead of []. becuase i have to specify the deleted record and then update the records. so what i should put instead of [], so i wont get input message. everytime i delete record and click update. i get input message in where i have to specify what number i have deleted. is there any way i can specify my current deleted record instead of [].and rest of the records updates automatically.

thanks

[NeoPa]

Try :

UPDATE MemberData
SET Seniority = Seniority-1
WHERE Seniority>4;

The value here would be the value of the Seniority field of the record just deleted.