In search of an SQL query to move the contents of one table into another table
I was so proud of myself: Getting the exact query i wanted basically on the first try:
select * from taxonomy_term__parent where delta=0 AND entity_id IN (SELECT entity_id FROM taxonomy_term__parent WHERE delta=1);
+---------------+---------+-----------+-------------+----------+-------+------------------+
| bundle | deleted | entity_id | revision_id | langcode | delta | parent_target_id |
+---------------+---------+-----------+-------------+----------+-------+------------------+
| resource_type | 0 | 9 | 9 | en | 0 | 0 |
| resource_type | 0 | 11 | 11 | en | 0 | 0 |
| resource_type | 0 | 13 | 13 | en | 0 | 0 |
| resource_type | 0 | 14 | 14 | en | 0 | 0 |
| resource_type | 0 | 15 | 15 | en | 0 | 0 |
| resource_type | 0 | 39 | 39 | en | 0 | 0 |
| resource_type | 0 | 40 | 40 | en | 0 | 0 |
| resource_type | 0 | 42 | 42 | en | 0 | 0 |
+---------------+---------+-----------+-------------+----------+-------+------------------+
(really i ultimately wanted the ones i kept to have a delta of 0, but it is easier to keep track of the duplicates with the delta of 1, so i’m deleting the delta 0s first, to remove the duplicates, and then i’ll fix things up more)
But…
DELETE from taxonomy_term__parent where delta=0 AND entity_id IN (SELECT entity_id FROM taxonomy_term__parent WHERE delta=1);
ERROR 1093 (HY000): Table 'taxonomy_term__parent' is specified twice, both as a target for 'DELETE' and as a separate source for data
So next-best thing is, as always, copy-paste.
SELECT GROUP_CONCAT(entity_id SEPARATOR ',') FROM taxonomy_term__parent WHERE delta=1 GROUP BY NULL;
+---------------------------------------+
| GROUP_CONCAT(entity_id SEPARATOR ',') |
+---------------------------------------+
| 9,11,13,14,15,39,40,42 |
+---------------------------------------+
gives me something i can copy and paste into a delete statement.
Test as a SELECT statement first:
SELECT * from taxonomy_term__parent where delta=0 AND entity_id IN (9,11,13,14,15,39,40,42);
And let’s do it:
DELETE from taxonomy_term__parent where delta=0 AND entity_id IN (9,11,13,14,15,39,40,42);
Credit to:
https://stackoverflow.com/questions/1728417/how-can-i-concatenate-set-of-results-in-mysql
More cleanup:
MariaDB [family_home_live]> INSERT INTO node__field_topics (bundle, deleted, entity_id, revision_id, langcode, delta, field_topics_target_id) SELECT bundle, deleted, entity_id, revision_id, langcode, delta, field_resource_type_target_id FROM node__field_resource_type;
Credit to:
https://stackoverflow.com/a/2343652/1028376
… and one more…