No need to defrag/compact...MySQL will reuse the space.
Since posting my original message, I've learned that version 4.0 (alpha) of
MySQL was released on 15 October. In Paragraph 6.4.6 of the MySQL manual,
we read:
The multi table delete format is supported starting from MySQL 4.0.0.
Bottom line: if you don't mind unstable, ever-changing, in-development
application for mission-critical applications, you can use vers 4.0.
Otherwise, you need to follow the algorithm you've laid out. Or, if your
web-host has the option, investigate table of TYPE=INNODB. These tables
support FOREIGN keys which may allow multi-table deletion. In version 3.23,
your web-host must be running the MYSQLD-MAX binary. In version 4.0, INNODB
support is in all binaries.
-----Original Message-----
From: Shaun Honsvick [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 6:35 PM
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Newbie Question: Convert SELECT to DELETE
statement in mysql
No, I am not trying to delete with a select statement. That was just there
to show my tables, fields, etc.
So if I understand what you are saying I can not do a delete statement with
a join in it. I would have to query each individual row for the ID that need
to be deleted, delete the row in the first table, then delete the row in the
second table, third table, etc. Then loop for the next intance. Theres got
to be a better way. Also, after this is done should I run some sort compact
or defrag on the database that restore wasted space?
Thanks,
Shaun
"Rick Emery" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]...
> Are you trying to delete records with a SELECT statement? That can't be
> done.
>
> To delete from two different tables, you must use two different DELETE
> statements.
>
> -----Original Message-----
> From: Shaun Honsvick [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, October 16, 2001 3:59 PM
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] Newbie Question: Convert SELECT to DELETE statement in
> mysql
>
>
> I am trying to run a delete on a mysql database. The delete query needs to
> delete from 2 tables. The select statement is:
>
> SELECT tinvoices.*, ttrackingnumbers.* FROM tinvoices LEFT JOIN
> ttrackingnumbers ON tinvoices.InvoiceID = ttrackingnumbers.InvoiceID WHERE
> Date < '2001-08-01';
>
> I can't seem to get a delete statement to work. (This is my first project)
>
> Thanks,
> Shaun
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
