MySQL subquery will not work

Question

I have problems with subquerys. In phpMyAdmin when I enter this:

SELECT companys.id 
  FROM companys 
  JOIN users 
    ON users.company = companys.id 
 WHERE users.id = '$LOGGED_IN_USER'

I get number 1 returned. That is correct.

And then I tested:

UPDATE companys 
   SET companys.signature = '$SIGNATURE' 
 WHERE companys.id = 1

That also works, it updates the value with $SIGNATURE where companys.id = 1. So far so good. Now to the problem. The problem appears when I combine those two:

UPDATE companys 
   SET companys.signature = '$SIGNATURE' 
 WHERE companys.id = ( SELECT companys.id 
                         FROM companys 
                         JOIN users 
                           ON users.company = companys.id 
                        WHERE users.id = '$LOGGED_IN_USER')

The DB never gets updated. It is like the inner select doesn't work suddenly for some reason.

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Aug 09 '15 at 20:50
This is done in context of PHP, it would seem. And if you don't see an error it is because you aren't logging/displaying errors or even checking for them. MysQL probably disallows this query because you are trying to modify the `companys` table while also using it in the `WHERE` clause. That can't be done without a join. — Michael Berkowski, Aug 09 '15 at 20:53
You need to check the relevant error method for the API you're using `mysql_error(), mysqli_error(), `PDO::errorInfo()` — Michael Berkowski, Aug 09 '15 at 20:54

score 0 · Accepted Answer · answered Aug 09 '15 at 20:56

With the update query you have you should be getting an error: You can't specify target table 'companys' for update in FROM clause

One solution is to force MySQL to create a temporary result set that it can use as the source:

UPDATE companys 
   SET companys.signature = '$SIGNATURE' 
 WHERE companys.id = ( 
   SELECT id FROM (
     SELECT companys.id 
     FROM companys 
     JOIN users 
     ON users.company = companys.id 
     WHERE users.id = '$LOGGED_IN_USER'
   ) src
 );

See this SQL fiddle for a working example.

MySQL subquery will not work

1 Answers1