phpmyadmin Question posted in
The official documentation can be found here.

SQL: Unable to use UPDATE clause after using a UNION or subquerym while a similar SELECT still works – Phpmyadmin

So I currently have a database with the following structure:

  • wp_giantbomb_game_list(post_id, important)

  • wp_game_attribute_company(ID, important)

  • wp_game_relation_publisher(ID, game_id, attribute_id)

  • wp_game_relation_developer(ID, game_id, attribute_id)

The important tag is a boolean that says whether or not a game is important. What I currently do is to set companies as important manually. I want to now create a query that sets all games as important, that either have a developer or publisher set as important. The publisher and developer relations are given through the “game_relation” tables, where the game_id links to the “wp_giantbomb_game_list” and the “attribute_id”.

Thus, my first objective was to try and combine the data from both relation tables into one. It was irrelevant to me whether or not the publisher or developer was set as “important” after all.

I used the following query to return all the “game_id” tags that happen to either have an important publisher or developer

SELECT PUB.game_id FROM wp_game_relation_publisher PUB INNER JOIN 
wp_game_attribute_company COM ON PUB.attribute_id = COM.ID WHERE COM.important = 1 
UNION SELECT DEV.game_id FROM wp_game_relation_developer DEV INNER JOIN
wp_game_attribute_company COM ON DEV.attribute_id = COM.ID WHERE COM.important = 1 
ORDER BY game_id ASC;

This query was very quick and did its purpose. Now I simply wanted to use the game_id with a WHERE IN clause ala

SELECT * FROM `wp_giantbomb_game_list` GAMELIST WHERE GAMELIST.post_id IN (
    SELECT PUB.game_id FROM wp_game_relation_publisher PUB INNER JOIN 
    wp_game_attribute_company COM ON PUB.attribute_id = COM.ID WHERE COM.important = 1 
    UNION SELECT DEV.game_id FROM wp_game_relation_developer DEV INNER JOIN
    wp_game_attribute_company COM ON DEV.attribute_id = COM.ID WHERE COM.important = 1 
    ORDER BY game_id ASC);

This query didn’t work whatsoever. PHPMyAdmin just didn’t respond at all causing me to reload the site. I then tried a workaround without the UNION by simply adding in an OR in the WHERE IN clause.

SELECT * FROM `wp_giantbomb_game_list` GAMELIST WHERE GAMELIST.post_id IN(
    SELECT PUB.game_id FROM wp_game_relation_publisher PUB INNER JOIN
    wp_game_attribute_company COM ON PUB.attribute_id = COM.ID WHERE COM.important = 1)
    or GAMELIST.post_id IN (SELECT DEV.game_id FROM wp_game_relation_developer DEV INNER JOIN
    wp_game_attribute_company COM ON DEV.attribute_id = COM.ID WHERE COM.important = 1 );

This worked, but then using a comparable update statement caused PHPMyAdmin to simply not react anymore.

UPDATE `wp_giantbomb_game_list` GAMELIST SET important = 1 WHERE GAMELIST.post_id IN(
    SELECT PUB.game_id FROM wp_game_relation_publisher PUB INNER JOIN
    wp_game_attribute_company COM ON PUB.attribute_id = COM.ID WHERE COM.important = 1)
    or GAMELIST.post_id IN (SELECT DEV.game_id FROM wp_game_relation_developer DEV INNER JOIN
    wp_game_attribute_company COM ON DEV.attribute_id = COM.ID WHERE COM.important = 1 );

So now I simply have no idea what is going. My question thus is, does anyone know what is going on and does anyone know how to fix this problem?

Huge thanks to anyone coming here in advance!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Right, I tried using your code and it had the same issue. But that got me to dig a little and I saw that I didn't have any indexes on the foreign keys. Added a couple of BTREE indexes to the foreign keys and now both mine and the code given in the answers works perfectly fine.

    Big thanks for the help and sorry for the inconvenience!


  2. 0

    Try using EXISTS:

    UPDATE `wp_giantbomb_game_list` gl
    SET important = 1
    WHERE EXISTS (SELECT 1
                  FROM wp_game_relation_publisher p INNER JOIN
                       wp_game_attribute_company c
                       ON p.attribute_id = c.ID
                  WHERE c.important = 1 AND gl.post_id = p.game_id
                 ) OR
          EXISTS (SELECT 1
                  FROM wp_game_relation_developer d INNER JOIN
                       wp_game_attribute_company c
                       ON d.attribute_id = c.ID 
                  WHERE d.important = 1 AND gl.post_id = d.game_id
                 ) ;

    Then, this can take advantage of indexes on:

    • wp_game_relation_publisher(post_id, attribute_id)
    • wp_game_attribute_company(id, important)
    • wp_game_relation_developer(game_id, attribute_id)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search