Limit in sub query

Question

I have made a query as below but MYSQL is returning error saying

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

How can rewrite this query to get around this problem?

Thanks.

I tried using keyword 'IN' in the query with LIMIT 10

  SELECT
    'Y',
   `account`,
  `awb`,
  `hawb`,
  `service`,
  `handling`,
  `reference`,
  `date_submitted`,
  `date_imported`,
  `date_printed`,
  `printed_file_id`,
  `date_received`,
  `date_booked`,
  `booked_file_id`,
  `date_exported`,
  `export_file_id`,
  `company`,
  `contact`,
  `address_line_1`,
  `address_line_2`,
  `address_line_3`,
  `id` 
  FROM
  `consignment` WHERE `consignment`.`processed`=1 AND `eamglo5_singaporelive`.`consignment`.id NOT IN ( SELECT eamglo5_billingsystem.`consignment`.`refrence_id` FROM eamglo5_billingsystem.`consignment` limit 10) limit 10

Expected: Should execute the query with no problem Actual: Getting error Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Possible duplicate of [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu) — Clive, Jul 21 '19 at 10:34

score 1 · Accepted Answer · answered Jul 21 '19 at 10:52

1

You could try using a left join on the subquery (instead of the IN clause) and check for not matching result

  SELECT
    'Y',
   `account`,
  `awb`,
  `hawb`,
  `service`,
  `handling`,
  `reference`,
  `date_submitted`,
  `date_imported`,
  `date_printed`,
  `printed_file_id`,
  `date_received`,
  `date_booked`,
  `booked_file_id`,
  `date_exported`,
  `export_file_id`,
  `company`,
  `contact`,
  `address_line_1`,
  `address_line_2`,
  `address_line_3`,
  `id` 
  FROM  `eamglo5_singaporelive`.`consignment` 
  left join  (
    SELECT eamglo5_billingsystem.`consignment`.`refrence_id` 
     FROM eamglo5_billingsystem.`consignment` 
     limit 10
  ) t  ON  t.refrence_id =`eamglo5_singaporelive`.`consignment`.id 
  WHERE `eamglo5_singaporelive`.`consignment`.`processed`=1 
  and t.refrence_id is null 
  limit 10

anyway a limit without order by is not a good solution

answered Jul 21 '19 at 10:52

ScaisEdge

131,976
10
91
107

Lets say i want to bring in all rows in `eamglo5_singaporelive`.`consignment` into eamglo5_billingsystem.`consignment` which has column 'processed'='1' and date_booked >'2018-07-17', how can i do that? – user3855851 Jul 21 '19 at 11:28
@user3855851 . . . The question in your comment seems to have nothing to do with this answer or your question. New questions should be asked as *questions* not *comments*. – Gordon Linoff Jul 21 '19 at 11:49
https://stackoverflow.com/questions/57133033/mysql-copy-data-from-one-table-to-another-with-specific-condition – user3855851 Jul 21 '19 at 12:03

score 0 · Answer 2 · answered Jul 21 '19 at 10:53

You have this in your query

slc.id NOT IN (SELECT bsc.reference_id
               FROM eamglo5_billingsystem.consignment bsc 
               LIMIT 10
              )

I have simplified the query by using table aliases.

First, you are using LIMIT with no ORDER BY. This is not a good idea.

Second, in this case, the query is not correlated, so you can move it to the FROM clause:

FROM consignment c LEFT JOIN
     (SELECT bsc.reference_id
      FROM eamglo5_billingsystem.consignment bsc 
      LIMIT 10
     ) bsc
     ON c.id = bsc.reference_id
WHERE c.processed = 1 AND 
      bsc.reference_id IS NULL

Lets say i want to bring in all rows in eamglo5_singaporelive.consignment into eamglo5_billingsystem.consignment which has column 'processed'='1' and date_booked >'2018-07-17', how can i do that? — user3855851, Jul 21 '19 at 11:44

Limit in sub query

2 Answers2