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

[code=sql]
INSERT INTO price_breaks
(part,price_lis t,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

[/code]

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.