By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,434 Members | 2,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,434 IT Pros & Developers. It's quick & easy.

INSERT ... SELECT ... ON DUPLICATE KEY UPDATE not working for me.

P: n/a
Hello team. I'm running mysql 4.0.20-standard and i'm trying to do a
query like this:

INSERT IGNORE
INTO `DonorPledges` (
nDonor_id,
nPledgeYear,
nTotalPaid,
nTotalPledge )
SELECT
DonorInfo.nDonor_id,
'2006',
DonorHolding.nSysYrPaid,
(
DonorHolding.nJan+DonorHolding.nFeb+DonorHolding.n Mar+

DonorHolding.nApr+DonorHolding.nMay+DonorHolding.n Jun+

DonorHolding.nJul+DonorHolding.nAug+DonorHolding.n Sep+

DonorHolding.nOct+DonorHolding.nNov+DonorHolding.n Dec )
FROM `DonorHolding`
LEFT JOIN `DonorInfo` ON

`DonorHolding`.`nMemberID`=`DonorInfo`.`nMemberID`
ON DUPLICATE KEY UPDATE
nTotalPaid = DonorHolding.nSysYrPaid,
nTotalPledge =
(
DonorHolding.nJan+DonorHolding.nFeb+DonorHolding.n Mar+

DonorHolding.nApr+DonorHolding.nMay+DonorHolding.n Jun+

DonorHolding.nJul+DonorHolding.nAug+DonorHolding.n Sep+

DonorHolding.nOct+DonorHolding.nNov+DonorHolding.n Dec )

I've stripped it down a bit, because it's an extremely verbose query,
but that is all of the main elements of it. The schema is exported
below (also stripped down a bit). Is it my syntax? Can i not do left
joins when doing an insert...select...on dup key upd? Is it my mysql
version? Anyone who could point me at any useful info would be my
hero. The mysql docs on the subject are not as clear as they could be,
and i'm unsure as to whether or not i'm following them to spec. Thank
you very much!

-Brendan

Schema:

CREATE TABLE DonorHolding (
nMemberID int(20) unsigned NOT NULL default '0',
nJan int(10) unsigned NOT NULL default '0',
nFeb int(10) unsigned NOT NULL default '0',
nMar int(10) unsigned NOT NULL default '0',
nApr int(10) unsigned NOT NULL default '0',
nMay int(10) unsigned NOT NULL default '0',
nJun int(10) unsigned NOT NULL default '0',
nJul int(10) unsigned NOT NULL default '0',
nAug int(10) unsigned NOT NULL default '0',
nSep int(10) unsigned NOT NULL default '0',
nOct int(10) unsigned NOT NULL default '0',
nNov int(10) unsigned NOT NULL default '0',
nDec int(10) unsigned NOT NULL default '0',
nSysYrPaid float NOT NULL default '0',
nSysYrPRDedPledge int(11) NOT NULL default '0',
UNIQUE KEY iMemberID (nMemberID)
) TYPE=MyISAM COMMENT='The holding information for data import';

CREATE TABLE DonorInfo (
nDonor_id int(11) unsigned NOT NULL auto_increment,
nLogin_id int(11) NOT NULL default '0',
nMemberID varchar(64) NOT NULL default '',
nSysYrPaid float NOT NULL default '0',
nSysYrPRDedPledge int(11) NOT NULL default '0',
PRIMARY KEY (nDonor_id),
UNIQUE KEY iMemberID (cEnvisageMemberID)
) TYPE=MyISAM COMMENT='The basic information for a Donor';

CREATE TABLE DonorPledges (
nDonor_id int(11) unsigned NOT NULL default '0',
nPledgeYear int(4) NOT NULL default '0',
nTotalPledge float NOT NULL default '0',
nTotalPaid float NOT NULL default '0',
PRIMARY KEY (nDonor_id,nPledgeYear)
) TYPE=MyISAM COMMENT='Donor Pledge info, 1:1 with DonorInfo';

Feb 8 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.