insert into

  • 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

  • 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