Adding locationID's to detail records using SQL syntax

SQLserver 2K
Table A (Customer Master)
CustID
CustName
Table B (Customer Location)
CustID
LocationID
LocationOrderID
LocationName
What's the syntax to automatically add LocationOrderID in increment of 1 for
table B where B.CustID = A.CustID ?
For example,
A.CustID = 100
and there are 5 B records having B.CustID = 100
I need to insert LocationOrderID starting with 1 to B.LocationOrderID based
on the order of B.LocationName
Any help is greatly appreciated.
BillTry,
update tableB
set LocationOrderID = (select count(*) from tableB as a where a.CustID =
tableB.CustID and a.LocationName <= tableB.LocationName)
go
AMB
"Bill Nguyen" wrote:

> SQLserver 2K
> Table A (Customer Master)
> CustID
> CustName
> Table B (Customer Location)
> CustID
> LocationID
> LocationOrderID
> LocationName
> What's the syntax to automatically add LocationOrderID in increment of 1 f
or
> table B where B.CustID = A.CustID ?
> For example,
> A.CustID = 100
> and there are 5 B records having B.CustID = 100
> I need to insert LocationOrderID starting with 1 to B.LocationOrderID base
d
> on the order of B.LocationName
>
> Any help is greatly appreciated.
> Bill
>
>|||Alejandro;
This works great!
Thanks
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9E0D3718-1900-409C-BAFB-885F32212EB9@.microsoft.com...
> Try,
> update tableB
> set LocationOrderID = (select count(*) from tableB as a where a.CustID =
> tableB.CustID and a.LocationName <= tableB.LocationName)
> go
>
> AMB
> "Bill Nguyen" wrote:
>