Hi, I m using MySQL 4.0.2 which now supports multi-table update query
I am getting error while throwing multi-table UPDATE query, I don't know what the problem is To generate an error run below query, it will create a 2 InnoDB table and inserts a dummy record in it CREATE TABLE `systemdefault` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=InnoDB; CREATE TABLE `userpreference` ( `id` int(11) NOT NULL auto_increment, `SysDefaultId` int(11) default NULL, `fld1` int(11) default NULL, `fld2` tinyint(1) unsigned default NULL, `OperatorId` int(11) default NULL, PRIMARY KEY (`id`), KEY `ix_SysDefaultId_userpreference` (`SysDefaultId`), FOREIGN KEY (`SysDefaultId`) REFERENCES `test.systemdefault` (`id`) ) TYPE=InnoDB; INSERT INTO systemdefault VALUES(1), (2), (3); INSERT INTO userpreference(sysdefaultid,fld1,fld2,operatorid) VALUES(1,99,NULL,NULL), (2,88,0,NULL), (3,77,1,NULL), (1,NULL,NULL,1), (2,NULL,NULL,1),(3,NULL,NULL,1); Now throw following UPDATE query UPDATE UserPreference LEFT JOIN UserPreference AS Preference ON UserPreference.SysDefaultId = Preference.SysDefaultId SET UserPreference.fld1 = Preference.fld1, UserPreference.fld2 = Preference.fld2 WHERE Preference.OperatorId IS NULL OR UserPreference.OperatorId = 1; It throws following Error ERROR 1105: Unknown error Anybody have an idea wht's wrong in above UPDATE statement? THX IN ADVANCE - Deep __________________________________________________ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
