Remove duplicates and connect relations to the first one Guess I can use some help over here. I have split a products table into two tables, products and prices. This is the original table:

products:

+----+--------+-----------+-----------+-----------+
| ID |  sku   |    name   | price_old | price_new |
+----+--------+-----------+-----------+-----------+
| 10 |  1234  |   shirt   |   15.00   |   10.00   |
+----+--------+-----------+-----------+-----------+
| 11 |  1234  |   shirt   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+
| 12 |  5678  |   jeans   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+


To start, I first copied some information to the second table. So my structure at the moment is:

products:

+----+--------+-----------+-----------+-----------+
| ID |  sku   |    name   | price_old | price_new |
+----+--------+-----------+-----------+-----------+
| 10 |  1234  |   shirt   |   15.00   |   10.00   |
+----+--------+-----------+-----------+-----------+
| 11 |  1234  |   shirt   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+
| 12 |  5678  |   jeans   |   80.00   |   60.00   |
+----+--------+-----------+-----------+-----------+


prices:

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 11 |     10     |   15.00   |   10.00   |
+----+------------+-----------+-----------+
| 12 |     11     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 13 |     12     |   80.00   |   60.00   |
+----+------------+-----------+-----------+


But now the duplicates need to be deleted from products (all products with same name  are duplicates), and their connections inside offers need to be connected to the first one. So this should become the end result:

products:

+----+--------+-----------+
| ID |  sku   |    name   | 
+----+--------+-----------+
| 10 |  1234  |   shirt   |
+----+--------+-----------+
| 12 |  5678  |   jeans   |
+----+--------+-----------+


prices:

+----+------------+-----------+-----------+
| ID | product_id | price_old | price_new |
+----+------------+-----------+-----------+
| 11 |     10     |   15.00   |   10.00   |
+----+------------+-----------+-----------+
| 12 |     10     |   80.00   |   60.00   |
+----+------------+-----------+-----------+
| 13 |     12     |   80.00   |   60.00   |
+----+------------+-----------+-----------+


As you can see ID 12 in prices, becomes connected to product_id 10 instead of 11 and de duplicate of 10 (which was 11) is deleted from products. It was a duplicate because it was the same product, only the prices were different. I would like to put the prices in a separate table and the rest of the product information inside a products table.

Anyone who can help me with a query?
Well,
Apologies to all those SQL guru's - this might be a simplistic answer. 

Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key.

Insert into that table a distinct list of the products
 e.g 

INSERT INTO products (sku,ProductName)
SELECT DISTINCT (sku,ProductName)
FROM OldProductTable


That's your product table sorted.

How can you tell from your original data which price is the latest price? You have potentially two prices it could be. Lets presume the latest ID is the latest price. 

Now you need to create a price table (as you have done) and create your foreign keys on it. You need some way of telling which is the current price though. One way is to give each record an iteration column, so the highest value in it is the latest price. Before you scream - this is a bit rubbish but is the only way I can see it fitting your data.

INSERT INTO ProductPrices (sku, PriceOld, PriceNew, iteration)
SELECT sku, PriceOld, PriceNew, ID
FROM OldProductTable


The primary key is sku and iteration. You want the latest price you just select the product and return the record with the highest iteration. 

A better way to do it would be to have the concept of recording the price that was valid  date range:

Product ID,Price,StartDate,EndDate


Live prices will not have an end date (null). When you want to change a price you simply update the end date and add a new record with the start date being the date the new price comes in to effect.

That's just me though! Be happy to see other peoples views.