Hi,
Unfortunately, I think you have already found the 'correct' way to do it in
MySQL. At the moment, Deletes involving joins or sub-selects are not
supported.
Regards
Quentin
-----Original Message-----
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 15 February 2001 14:26
To: James R. Butzen; mySQL Database
Subject: RE: how do I delete using a join in mySQL?
> If I recall it right the statement should be following:
>
> DELETE FROM Contract_Table, Client_Table WHERE contract_client_id =
> client_id
> AND client_contact = 'corricello' AND contract_percent = 0;
I made a copy of the database and tried this one, but it caused an error.
I'm thinking something like this is what I would expect the Query to be:
DELETE FROM Contract_Table WHERE contract_id = (SELECT contract_id,
client_contact, contract_percent FROM Contract_Table, Client_Table WHERE
contract_client_id = client_id AND client_contact = 'corricello' AND
contract_percent = 0);
however that didn't work either...
for now, I just wrote a little PHP function to do it. I just feel that there
should have been a way to do it in mySQL.
but can someone educate me on the proper way to do this with a single mySQL
statement?
#!/bin/php -q
<?php
//version date 02.14.01
Error_Reporting(15); // Set this to 7 for more error reporting...
$linkid = mysql_connect( "host", "user", "pw");
$sql = "SELECT contract_id, client_contact, contract_percent FROM
Contract_Table, Client_Table WHERE contract_client_id = client_id AND
client_contact = 'corricello' AND contract_percent = 0";
echo "\n".$sql."\n";
$result = mysql_db_query("sales_contracts", $sql, $linkid);
$count = mysql_num_rows($result);
for ( $i = 0; $i < $count; $i++)
{
$row = mysql_fetch_array($result);
$sql = "DELETE FROM Contract_Table WHERE contract_id =
".$row[0];
echo $sql."\n";
$deleteresult = mysql_db_query("sales_contracts", $sql,
$linkid);
}
echo "Finished\n\n";
mysql_close($linkid);
?>
> -----Original Message-----
> how do I DELETE all these Contract_Table records in mySQL?
>
> SELECT contract_id, client_contact, contract_percent
> FROM Contract_Table, Client_Table
> WHERE contract_client_id = client_id
> AND client_contact = 'corricello' AND contract_percent = 0;
>
>
> I see this page, but didn't know if it was dated or not:
> http://www.mysql.com/doc/D/e/Deleting_from_related_tables.html
>
> Does v3.23 support this if v3.22 doesn't?
>
> daevid.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
