insert into

• smfiber

  SSC Veteran

  Points: 235

  More actions

  October 17, 2002 at 6:15 pm

  #101279

  Hi,

  I have 2 tables. I want to insert into one table where min(cost) is calculated based on 1 column that is the same. 'PartNumber'

  For example.

  My tables: TABLE1, TABLE2

  Similar Columns are:

  Cost & PartNumber

  I want to insert into TABLE2 where cost is lowest based on ONLY similar part numbers.

  select

  PartNumber, min(COST) as low_cost

  from (select PartNumber, COST from TABLE1

  union

  select PartNumber, COST from TABLE2) x

  WHERE table1.PartNumber=table2.PartNumber

  group by PartNumber

  GO

  INSERT INTO TABLE2(COST2) VALUES('LOW_COST')

  WHERE TABLE1.PARTNUMBR=TABLE2.PARTNUMBR

  ANY IDEAS??

  Thanks,

  Andrew

  Andrew

  http://eshopsoho.com

  
  Andrew
  http://eshopsoho.com

• Mike2265

  Right there with Babe

  Points: 723

  More actions

  October 17, 2002 at 9:36 pm

  #438845

  I'm confused.

  Are you sure you're wanting to do an insert and not an update to specify which value is the low cost?

  Something similar to this came up recently...here we go - it seems it was yours

  I'd use the full outer join that Julian specified but going with your union remove the table join where clause as it won't work and isn't required and put at the front of this statement the Insert Table2.

  Something like this...

  Insert Table2

  select

  --these must have valid Table2 names

  PartNumber, min(COST) as low_cost ,'LOW_Cost' as CostDescription

  from

  (select PartNumber, COST from TABLE1

  union

  select PartNumber, COST from TABLE2) x

  group by PartNumber

  Cheers,

  Mike