You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-user@james.apache.org by Chris Simmons <ch...@NetChris.com> on 2004/05/31 05:07:53 UTC
MySQL Question
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
Re[2]: MySQL Question
Posted by 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
Re: MySQL Question
Posted by Lane Sharman <la...@opendoors.com>.
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
RE: MySQL Question
Posted by "Noel J. Bergman" <no...@devtech.com>.
> I'd like to "dump" the deadletter table back into the spool
See the FromRepository mailet.
--- Noel
---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org