October 17, 2002 at 6:15 pm
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
Andrew
http://eshopsoho.com
October 17, 2002 at 9:36 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply