merge data from two sets into one

[code green]

I have a table price_breaks with different prices identified by price_list ie:

part    price_list      price
401        WSPL                1.6
401        LEVEL-70         1.3
404        WSPL                3.0
404        LEVEL-70         3.3
502        WSPL                2.0
600        LEVEL-70      4.0
 

I need to INSERT new records INTO this table,
which is a combination of the two price-lists as a new price_list WSPL70.
The criteria being
1. If a price for WSPL and LEVEL-70 exists, use the WSPL price
2. If only one price exists use that

I can JOIN to another table called products with the same parts.
The WSPL70 prices must be updated weekly from the same table
with any new products inserted
So I thought along the lines of

DELETE FROM price_breaks WHERE price_list = 'WSPL70';
 
INSERT INTO price_breaks
(part,price_list,price)
VALUES
(SELECT COALESCE (pl.part,alt.part) part
'WSPL70' price_list,
COALESCE (pl.price,alt.price) price,
FROM products
JOIN price_breaks AS pl 
ON (part.part = pl.part 
AND pl.price_list = 'WSPL')
LEFT JOIN price_breaks AS alt 
ON (part.part = alt.part 
AND alt.price_list = 'LEVEL-70'
AND pl.price_list IS NULL))

I have only tested the SELECT sub-query so far which doesn't return any 'LEVEL-70' prices
because the first JOIN filters them out.
If I change this to a LEFT JOIN
every part in products is returned with NULL values
whereas a RIGHT JOIN returns all the other price_lists (not shown in eg)

Can anybody help with this
Also, can I INSERT INTO a table with data from a sub-query from the same table as shown

[deepuv04]

Hi,
try using the following query

INSERT INTO price_breaks
(part,price_list,price)
SELECT  distinct t.part,'WSPL70',
        case when wspl_Price is not null and level70_price is not null then wspl_price
             when wspl_Price is not null and level70_price is  null then wspl_price
             else level70_price end as price                                    
FROM price_breaks as t left outer join
        (select part,PRICE as wspl_Price from price_breaks as t1 where price_list = 'wspl'
        ) AS t1 on t1.part = t.part left outer join
        (select part,price as level70_price from price_breaks as t1 where price_list = 'Level-70' 
        ) as  t2 on t2.part = t.part
 
 

Thanks

[code green]

Different approach.
It was still producing null prices, probably from the other price_lists in the table.
(There are about 20 altogether) so I amended the query a little

SELECT DISTINCT t.part,'WSPL70',
wspl_price, level70_price,
CASE WHEN wspl_price IS NOT NULL 
    THEN wspl_price
WHEN wspl_Price IS NULL 
    AND level70_price IS NOT NULL 
    THEN level70_price END AS price                                    
FROM price_breaks AS t 
LEFT OUTER JOIN
 (SELECT part,price AS wspl_price 
  FROM price_breaks AS t1 
    WHERE price_list = 'PCD-WSPL') AS t1 
ON t1.part = t.part 
LEFT OUTER JOIN
  (SELECT part,price AS level70_price 
  FROM price_breaks AS t2 
      WHERE price_list = 'LEVEL-70') AS t2 
ON t2.part = t.part
WHERE price_list = 'LEVEL-70' OR price_list = 'PCD-WSPL'
ORDER BY t.part

Basically I moved a CASE condition to a WHERE condition at the end.
The result looks promising.
Using DISTINCT is a cop out in my book because it is not as efficient as filtering the records beforehand.
But I can see why it is needed in this case.

Thanks very much deepuv04.