I'm using a query like this: insert into spool (message_name, repository_name, message_state, error_message, sender, recipients, remote_host, remote_addr, message_body, last_updated, message_attributes) select message_name, 'spool', 'root', null , sender, recipients, remote_host, remote_addr, message_body, last_updated, message_attributes from deadletter where repository_name = 'error' and last_updated between '2004-06-01 12:00:00' and '2004-06-01 13:00:00';
Geert Van Damme Tuesday, June 1, 2004, 10:04:22 AM, you wrote: LS> Joins may not be allowed in a delete statement. They are usually used LS> for selects. LS> try a subselect in the where clause: LS> delete from tableName LS> where tableName.fieldName in (select LS> anotherTableName.fieldName from LS> anotherTableName where ...) LS> -Lane LS> Chris Simmons wrote: >>While the MySQL question may be better posed in a MySQL-oriented group, >>maybe I could get advice if what I'm doing in the first place is the >>correct way. I'd like to "dump" the deadletter table back into the spool >>and, since my mail repository is MySQL, I figured I'd just run a quick SQL >>script. Here it is: >> >>SCRIPT BEGINS AFTER THIS LINE >> >>USE james; >> >>CREATE TEMPORARY TABLE TempTable (message_name varchar(200), >>repository_name varchar(255)); >> >>INSERT INTO TempTable ( message_name, repository_name ) >>SELECT message_name, repository_name >>FROM deadletter; >> >>INSERT INTO spool >> ( message_name, >> repository_name, >> message_state, >> error_message, >> sender, >> recipients, >> remote_host, >> remote_addr, >> message_body, >> last_updated, >> message_attributes ) >> >> >>SELECT >> deadletter.message_name, >> 'spool' AS repository_name, >> 'root' AS message_state, >> NULL AS error_message, >> deadletter.sender, >> deadletter.recipients, >> deadletter.remote_host, >> deadletter.remote_addr, >> deadletter.message_body, >> deadletter.last_updated, >> deadletter.message_attributes >>FROM >> deadletter, TempTable >>WHERE >> deadletter.message_name = TempTable.message_name >> AND >> deadletter.repository_name = TempTable.repository_name; >> >>DELETE FROM >> deadletter >>USING >> deadletter, TempTable >>WHERE >> deadletter.message_name = TempTable.message_name >> AND >> deadletter.repository_name = TempTable.repository_name; >> >>DROP TABLE TempTable; >> >>SCRIPT ENDS BEFORE THIS LINE >> >>My problem happens on the last DELETE statement. The error is: >>ERROR 1064 at line 42 in file: 'respool_deadletter.sql': You have an error >>in your SQL syntax near 'USING >> deadletter, TempTable >>WHERE >> deadletter.message_name = TempTable.message_' at line 3 >> >>I can't, for the life of me, figure out what's going wrong. Also, am I >>using the wrong mechanism in the first place to do what I want to do? >> >>-- >>Thanks, >>Chris Simmons >>[EMAIL PROTECTED] >> >> >> >> -- Best regards, Geert mailto:[EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
