Pete Dermott Pete Dermott - 1 year ago 82
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:


| product_id | content_id |
| 374 | 1919 |


| 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:

products_product(content_id, text_content)

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 = cpt.cmsplugin_ptr_id

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

Answer Source

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_ptr_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download