Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a bad sql construct error.

If I run the following query by itself it works fine:

(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%gmail.com')
UNION DISTINCT
(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
(SELECT email FROM inactive_clients));

However, if I try to run same query with like this:

INSERT INTO my_backup_table (account_id) VALUES ($aboveQuery);

I get the following error:

MySQL server version for the right syntax to use near 'UNION DISTINCT (SELECT DISTINCT a.id FROM accounts at line 1

I tried any number of parenthetical permutations and still received the same error.

share|improve this question
    
You don't need the word DISTINCT anywhere in that query and it would be beneficial to turn your NOT IN (SELECT) into a proper LEFT JOIN/IS NOT NULL for performance reasons. –  Aaron Brown Jul 11 '12 at 1:54
add comment

2 Answers 2

up vote 3 down vote accepted

You cannot use the INSERT INTO tblname (...) VALUES ... syntax

You must use the INSERT INTO tblname (...) SELECT ... syntax

Try the Following:

INSERT INTO my_backup_table (account_id)
SELECT id FROM
(
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%gmail.com')
    UNION DISTINCT
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
    (SELECT email FROM inactive_clients))
) A;
share|improve this answer
    
Lol, look familiar? It worked! Thanks again. –  Mike Purcell Jul 10 '12 at 21:58
    
LOL !!! You asked about this similar UNION before ( dba.stackexchange.com/a/19900/877 ) –  RolandoMySQLDBA Jul 10 '12 at 22:00
    
Ya, for a select into outfile. What keeps throwing me off is the 'derived' SELECT id. –  Mike Purcell Jul 10 '12 at 22:03
    
You need neither the enclosing SELECT id FROM (...) A, nor the DISTINCT –  ypercube Jul 10 '12 at 22:14
add comment

Rolando answered your question regarding the INSERT syntax, but I'd like to add some additional comments regarding the performance of this query. First, you are DISTINCTing too many times. You actually don't need DISTINCT anywhere in this query as UNION implies DISTINCT. I'm not sure if the optimizer will optimize away the unnecessary DISTINCTs, but consider this:

mysql> SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.00 sec)

mysql> SELECT 1 UNION ALL SELECT 1 UNION SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Additionally, NOT IN (SELECT) will execute the inner query for every row in the bounding query and is highly inefficient. I come across this on a weekly basis with developers on my team and it causes problems every time. Instead, rewrite the query as a LEFT JOIN. You're also going to get a performance hit from the unindexable LIKE %domain.com.

Another consideration is, since this query will likely take a "while," in MySQL's default transaction isolation level of REPEATABLE READ, many of the rows in the tables in the SELECT statement will be locked due to gap locks, so will not be updatable. For this reason, I suggest you run this query in READ-COMMITTED combined with MIXED or ROW based replication.

SET BINLOG_FORMAT=MIXED;
SET TRANSACTION ISOLATION LEVEL READ-COMMITTED;

INSERT INTO my_backup_table (account_id)
  SELECT a.id
  FROM accounts AS a
  WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
  AND a.email NOT LIKE '%gmail.com')
  UNION 
  SELECT a.id 
  FROM accounts AS a
  LEFT JOIN inactive_clients ic USING(email)
  WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%yahoo.com'
    AND ic.email IS NULL;

 SET TRANSACTION ISOLATION LEVEL (whatever it was before);
 SET BINLOG_FORMAT=(whatever it was before);

I hope this helps a bit.

share|improve this answer
    
Thanks for the more indepth info, it's exactly why I keep telling people at work that we need a DBA on staff. –  Mike Purcell Jul 11 '12 at 16:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.