add a number to my order line

• atzmon

  Newbie

  Points: 1

  More actions

  May 31, 2003 at 10:17 am

  #80955

  i have one table name "order" which is my order Header and ther is fileds id "IDorder". I have new table which is the Order lines call "Order_Line" which have fields "IDorder" and fields "Order_num" .

  I need that ery time that i insert raw to my order line it sholud jump.this is my examople:

  table : order

  idorder name

  1 xxxxxx

  2 yyyyyy

  Table :Order_Line.

  IDorder Order_num item

  1 1 222

  1 2 333

  2 1 111

  how can i insert raw to table order_line to order "1" and the Order_num should be 3 by the sql? both r index in that field? this should be Aoutomatic bt the sqlserver2000, every tim i insert raw with order numeber?

  Thanks for your help.

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  June 2, 2003 at 3:09 am

  #459317

  declare @IDorder int,@Order_num int

  set @IDorder=1

  select @Order_num = isnull(max(Order_num),0)+1 from order_line where IDorder = @IDorder

  insert into Order_Line (IDorder,Order_num)

  values (@IDorderm,@Order_num)

  Far away is close at hand in the images of elsewhere.
  Anon.