Bug #4509 | Inert Statement insert rows when it shouldn't be | ||
---|---|---|---|
Submitted: | 12 Jul 2004 0:06 | Modified: | 15 Aug 2004 20:31 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
Version: | 5.0 alpha | OS: | MacOS (Mac OS X 10.3.4) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[12 Jul 2004 0:06]
[ name withheld ]
[12 Jul 2004 0:12]
[ name withheld ]
Can re-write (and it works) as: INSERT tblRoster (League, Team, Player, Salary, Stint, SignedThru, SignDate) SELECT 1, 5, p.pKey, IFNULL(MAX(r.Salary), 10), IFNULL(MAX(r.Stint), 0) + 1, IFNULL(r.SignedThru, YEAR(CURDATE()) + 2), '2004-07-10' FROM tblplayers p LEFT JOIN tblRoster r ON p.pkey = r.player AND r.League = 1 AND r.Team = 5 WHERE p.pkey = 3905 AND NOT EXISTS (SELECT * FROM tblRoster WHERE League = 1 AND Team <> 5 AND Player = 3905) GROUP BY p.pKey; INSERT tblRoster (League, Team, Player, Salary, Stint, SignedThru, SignDate) SELECT 1, 2, p.pKey, IFNULL(MAX(r.Salary), 10), IFNULL(MAX(r.Stint), 0) + 1, IFNULL(r.SignedThru, YEAR(CURDATE()) + 2), '2004-07-10' FROM tblplayers p LEFT JOIN tblRoster r ON p.pkey = r.player AND r.League = 1 AND r.Team = 2 WHERE p.pkey = 3905 AND NOT EXISTS (SELECT * FROM tblRoster WHERE League = 1 AND Team <> 2 AND Player = 3905) GROUP BY p.pKey;
[13 Jul 2004 3:07]
Matthew Lord
Hi, Could you provide the create table statement for this table so that I can easily attempt to repeat the problem? Best Regards
[13 Jul 2004 5:09]
[ name withheld ]
CREATE TABLE `tblroster` ( `pKey` int(11) NOT NULL auto_increment, `League` int(11) NOT NULL default '0', `Team` int(11) NOT NULL default '0', `Player` int(11) NOT NULL default '0', `Salary` int(11) NOT NULL default '0', `Position` char(3) NOT NULL default '', `Stint` int(11) NOT NULL default '0', `SignedThru` int(11) NOT NULL default '0', `SignDate` date NOT NULL default '0000-00-00', `ReleaseDate` date default NULL, `Reserved` tinyint(1) NOT NULL default '0', `ReservedDate` date default NULL, PRIMARY KEY (`pKey`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `tblplayers` ( `pKey` int(11) NOT NULL auto_increment, `LastName` varchar(50) NOT NULL default '', `FirstName` varchar(50) NOT NULL default '', `Position` varchar(12) NOT NULL default '', `LahmanID` varchar(50) default NULL, `CBSID` int(11) default NULL, `CurrentMLBTeam` char(3) default NULL, `League` char(2) default NULL, `LastPlayedOn` date default NULL, PRIMARY KEY (`pKey`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
[15 Jul 2004 20:31]
Matthew Lord
Hi, Could you provide a dump of the tblroster table for me? Best Regards
[14 Feb 2005 22:54]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".