467,923 Members | 1,440 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

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

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
  • viewed: 1804
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jtw | last post: by
20 posts views Thread by Mark Harrison | last post: by
8 posts views Thread by nano2k | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.