[prev in list] [next in list] [prev in thread] [next in thread] 

List:       james-user
Subject:    MySQL Question
From:       "Chris Simmons" <chris () NetChris ! com>
Date:       2004-05-31 3:07:53
Message-ID: 74C5BE0949A4EE4FA8FEB6890B2AED2A0828F2 () bart ! netchris ! com
[Download RAW message or body]


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



["smime.p7s" (application/x-pkcs7-signature)]

[prev in list] [next in list] [prev in thread] [next in thread]