MySQL - INSERT INTO … SELECT … UNION

Question

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.

Answer 1

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;

Answer 2

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.