Fill a newly added column with ref number

Forum Moderators: open

Message Too Old, No Replies

Fill a newly added column with ref number

Depending on parent id

Red_Eye

10:23 am on Jan 5, 2010 (gmt 0)

I have 2 tables

ParentID,
Title,
Etc....

ChildID,
ParentID,
Ref,
Name,
etc...

I have just added a ref column to allow me to reorder the list of child entries.

What I want to do for the existing data is update the new ref column.

So if there are 10 child items where the parent ID is 1 they the ref column to 1,2,3,...10.

if there are 5 child items with the parent ID of 2 they need to have the ref column set to 1,2,3,4,5

Any suggestions for a query to do this?

Thanks in advance

whoisgregg

9:21 pm on Mar 26, 2010 (gmt 0)

I can't think of a method to do this using SQL alone. It would be pretty trivial to write a script that loops through and updates the records though.

Red_Eye

9:30 pm on Mar 26, 2010 (gmt 0)

Thanks for your reply. I did manage to do it with sql in the insert query I first us a select query to find the highest ref number for a given parent id then use that to insert the new row in the child table.