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]
