Re[2]: MySQL Question

[Geert Van Damme <Ge...@darling.be>]

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
>>web@NetChris.com
>>
>>
>>  
>>




-- 
Best regards,
 Geert                            mailto:Geert.vandamme@darling.be


---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org