Pete Dermott Pete Dermott - 2 months ago 9
MySQL Question

MySQL removing a link table and joining 3 tables into one

I have a database that has an extraneous database table for product text content which was added via a djangocms placeholder field. I am looking to move this content back into a single table.

My three tables are:

products_product:

+------------+------------+
| product_id | content_id |
+------------+------------+
| 374 | 1919 |
+------------+------------+


cms_cmsplugin:

+----------------+------+
| placeholder_id | id |
+----------------+------+
| 1919 | 2042 |
+----------------+------+


and cmsplugin_text:

+------------------+------------------+
| cmsplugin_ptr_id | body |
+------------------+------------------+
| 2042 | <p>some_html</p> |
+------------------+------------------+


I am looking to remove the link table and add the cmsplugin_text body directly into the products_product table with a field name of text_content. So far I've been looking at this questions and have come up with the following:

INSERT INTO
products_product(content_id, text_content)

SELECT
p.content_id,
cpt.body as text_content

FROM products_product p
JOIN cms_cmsplugin cms ON p.content_id = cms.placeholder_id
LEFT JOIN cmsplugin_text cpt ON cms.id = cpt.cmsplugin_ptr_id


Which unfortunately hasn't worked and just made a mess of my products_product table.

Answer

After you add the column in products_product update:

UPDATE products, cms_cmsplugin, cmsplugin_text 
SET products.body = cmsplugin_text.body
WHERE product.content_id = cmsplugin_text.placeholder_id
AND cmsplugin_text.id = cmsplugin_ptr_id