MySQL delete with subquery

firemankurt · 09-29-2006, 06:33 PM

OK... This is driving me crazy. I know I can do two seperate queries to accoomplish this but I should not have to.

I am trying to check the `MPAConfig` file to see if a record exists.
(If that record exists then the current user has admin rights in the program.)
If that record exists then go ahead and delete some records in a different table.

Code:

DELETE 
FROM  
      `MPApagers`  
WHERE
      1 = ( SELECT 1
            FROM 
                `MPAConfig`
            WHERE
                `Setting` = 'ProfileAdmins'
            AND
                `Par4` LIKE BINARY '%Z%' )
AND 
     `EMPNUM` = '9902'

MySQL responds:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1
FROM
`MPAConfig`
WHERE
`Setting`

spirit receiver · 09-30-2006, 04:43 AM

This probably won't be of much help, but I just tried to imitate your query and it works fine. The statement I used was

Code:

select * from indices where 1=(select 1 from shares where ISIN="DE0006231004");

I'm using MySQL 5.0.

jon23d · 09-30-2006, 06:02 AM

I believe that only version 4.1 and later supports subselects.