james-server-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lane Sharman <l...@opendoors.com>
Subject Re: MySQL Question
Date Tue, 01 Jun 2004 08:04:22 GMT
Joins may not be allowed in a delete statement. They are usually used 
for selects.

try a subselect in the where clause:

delete from tableName
where tableName.fieldName in (select anotherTableName.fieldName from 
anotherTableName where ...)

-Lane

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
>
>
>  
>

-- 
Lane Sharman
Providing Private and SPAM-Free Email
http://www.opendoors.com
858-755-2868




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


Mime
View raw message