I have a table with duplicate rows. I want to update the rows before I delete them just to me sure that I delete the good ones.
The delete query is this and works:
DELETE FROM cscart_products
WHERE updated_timestamp
NOT IN (SELECT *
FROM (SELECT MAX(n.updated_timestamp)
FROM cscart_products n
GROUP BY n.product_code) x)
Example table:
product_code product_type status updated_timestamp
DUSP1893 P A 1551
DUSP1893 P A 322
AH324444 P A 555
AH324444 P A 21332
Some products have the same product_code. I want to update those with a smaller updated_timestamp from status A to H.
Thanky you
First get the rows with smaller timestamp and then update those records, like this:
UPDATE cscart_products
INNER JOIN (
SELECT product_code pc, MIX(updated_timestamp) ts
FROM cscart_products
GROUP BY product_code
) t
ON cscart_products.product_code = t.pc
AND cscart_products.updated_timestamp = t.ts
SET status = 'H'
Inner query will give you rows of product_code having smaller timestamp.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加