update fields mysql table with value from other row for the same product id
I need to update a database table with value that is used in another row. I have a database with 4000 products, for every product there are 3 rows, 1 for every language (i work with 3 languages).
Now i need to copy the product title from the dutch language into the title field from the English product row and the French product row for the same product id. Some title field in English and Frensh are empty, but not al off them are empty.
So I have this fields product_id ; language_id ; product_title
Anyone can help me with the sql i need to have to do this for all my 4000 products?
Thank you, kind regards Pete
ps_product_lang is the name of the product table
the database is MySQL
id_product int(10) unsigned NO PRI NULL
id_shop int(11) unsigned NO PRI 1
id_lang int(10) unsigned NO PRI NULL
description text YES NULL
description_short text YES NULL
link_rewrite varchar(128) NO NULL
meta_description varchar(255) YES NULL
meta_keywords varchar(255) YES NULL
meta_title varchar(128) YES NULL
name varchar(128) NO MUL NULL
available_now varchar(255) YES NULL
available_later varchar(255) YES NULL
Something along these lines should work.
UPDATE ps_product_lang p1
SET name =
(SELECT name
FROM (SELECT * FROM ps_product_lang) p2
WHERE p2.`id_product` = p1.`id_product`
AND p2.`id_lang` = 6)
WHERE `id_lang` != 6
You need the extra nested select in there to prevent MySQL from reporting the following error:
Error Code: 1093 You can't specify target table 'p1' for update in FROM clause
链接地址: http://www.djcxy.com/p/37506.html上一篇: 消息通知应用数据库结构设计