MySQL Forums
Forum List  »  Newbie

Re: Use of target table in subquery for INSERT...SELECT Syntax
Posted by: Peter Brawley
Date: July 06, 2016 04:53PM

Your question presents puzzles other than the apparent mismatch between the docs and subquery acceptance.

First, your query raises an error in 5.6 and 5.7 ...

SELECT run_Id AS runId, queryCode FROM ( 
  SELECT DISTINCT qCode AS queryCode FROM temp_xml
) AS ...

because run_id is missing from the subquery.

Second, in my world, column names are made self-documenting where possible, but yours are so arbitrarily variable, they're almost certain to generate errors.

Third, the apparent requirement of the query is to insert into runqueries the rows that exist in temp_xml and not in runqueries. The conventional and simple way to do that is with a simple exclusion join ...

insert into runqueries(runid,querycode)
  select distinct run_id,qcode
  from temp_xml t 
  left join runqueries r on t.run_id=r.runid and t.qcode=r.querycode
  where r.runid is null;

.. so why all the subqueries?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.