Thanks again, raymond
-----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 14:54 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept? Jacob, Raymond A Jr wrote: > Thank you, > I was definitely on the wrong track on this one. > I annotated your commands to make sure that I understood what they > were doing. Are my comments correct? > --------------- > You have the wrong syntax. You can't mention a table in the WHERE > clause that wasn't in the FROM clause. Try > > DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; > /* deletes all records in data with cid equal cid in sidtemp but > leaves sidtemp unchanged */ Correct. > or > > DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; > /* deletes all records in data and sidtemp where cids are equal */ No, this only deletes from data. These 2 are supposed to be equivalent. Using this version of the syntax deletes rows from the tables named *before* the "FROM". Tables used to determine the matching rows come after the "FROM". The first version I gave uses an explicit JOIN, the second uses the implicit, comma join. I prefer explicit joins, but I included the implicit join because it seemed to be what you were trying. > ---------------------- > /* Will the USING clause work also? */ It should. > or > > /* looking that link below: */ > DELETE data FROM data USING data, sidtemp WHERE data.cid = > sidtemp.cid > or > DELETE data FROM data USING data, sidtemp JOIN sidtemp ON > data.cid = sidtemp.cid > > Is the above correct also? Almost. In the USING form, the tables which should lose rows go after "FROM", while the tables used to make the selection go after "USING". Hence, the query would be DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid; In general, the implicit join syntax (comma between tables with join condition(s) in the WHERE clause) seems easy to use, but frequently leads to trouble. The explicit join syntax (table JOIN table ON condition) is much clearer, which should help avoid mistakes. You should probably read the manual page describing JOIN syntax <http://dev.mysql.com/doc/refman/4.1/en/join.html>. > Thank you, > raymond Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
