Complex MySQL Update Query: Define two instances of many tables

Hello,
I am in need of help w/ a complex MySQL update query. The thing I am having trouble with is defining the tables before running the update query. I think I need two instances of each table involved for this query to work, but perhaps there is a better/different approach.

The purpose of the query: Update class details (start time, end time, etc) from one school term (410) to another (420) if the class section is the same.

I've taken a stab at it, but don't know if this would work or how to define the tables properly.

The Tables:
Classes
ClassDetails
MapClasstoEmp

Classes1 and Classes2 would be the two instances of the same table: Classes
MapClasstoEmp1 and MapClasstoEmp2 would be two instances of the table MapClasstoEmp
MapClasstoEmp1 and MapClasstoEmp2 would be two instances of the table MapClasstoEmp

UPDATE Classes1,Classes2,MapClasstoEmp1,MapClasstoEmp2,
ClassDetails1,ClassDetails2

SET ClassDetails2.StartTime=ClassDetails1.StartTime,
ClassDetails2.EndTime=ClassDetails1.EndTime,
ClassDetails2.RoomNo=ClassDetails1.RoomNo,
MapClasstoEmp2.EmpID=MapClasstoEmp1.EmpID

WHERE Classes1.SectionID=Classes2.SectionID AND Classes1.TermID='410'  and Classes2.TermID='420'

Thanks for your help constructing this query properly or recommending a different solution.
abernsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
You can't update multiple tables at once, but you can update a table using a JOIN and data from another table including a different instance of the same physical table.  You will have to do at least two update statements.  

How are the Classes and ClassDetails tables joined?  For example I will use a field called ID as PK of Classes and ClassID as FK of ClassDetails.  You will need to follow the same logic for MapClassToEmp.
UPDATE d2
SET d2.StartTime = d1.StartTime,
d2.EndTime=d1.EndTime,
d2.RoomNo=d1.RoomNo
FROM (Classes c2 INNER JOIN ClassDetails d2 ON c2.ID = d2.ClassID) 
LEFT JOIN (Classes c1 INNER JOIN ClassDetails d1 ON c1.ID = d1.ClassID)
ON c2.SectionID = c1.SectionID AND c2.TermID = '420' AND c1.TermID = '410';
 
-- Separate update query for MapClassToEmp
UPDATE m2
SET m2.EmpID = m1.EmpID
FROM (Classes c2 INNER JOIN MapClassToEmp m2 ON c2.ID = m2.ClassID) 
LEFT JOIN (Classes c1 INNER JOIN MapClassToEmp m1 ON c1.ID = m1.ClassID)
ON c2.SectionID = c1.SectionID AND c2.TermID = '420' AND c1.TermID = '410';

Open in new window

Kevin CrossChief Technology OfficerCommented:
AND this should probably be INNER JOIN -- I just misstyped.

(Classes c2 INNER JOIN ClassDetails d2 ON c2.ID = d2.ClassID)
LEFT JOIN (Classes c1 INNER JOIN ClassDetails d1 ON c1.ID = d1.ClassID)

Change to this:

(Classes c2 INNER JOIN ClassDetails d2 ON c2.ID = d2.ClassID)
INNER JOIN (Classes c1 INNER JOIN ClassDetails d1 ON c1.ID = d1.ClassID)

abernsAuthor Commented:
Hi,
Thanks for your help with this!
In answer to your question, ClassID is PK in Classes and FK in ClassDetails.

I ran the first query as you suggested (limiting to just one Class so that I could check it out more easily, and got the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID) INNER JO' at line 1
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

abernsAuthor Commented:
Here was the exact query: thanks again for your help!
UPDATE d2 SET d2.StartTime = d1.StartTime, d2.EndTime=d1.EndTime, d2.RoomNo=d1.RoomNo FROM (Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID) INNER JOIN (Classes c1 INNER JOIN ClassDetails d1 ON c1.ClassID = d1.ClassID) ON c2.SectionID = c1.SectionID AND c2.TermID = '420' AND c1.TermID = '410';

Open in new window

Kevin CrossChief Technology OfficerCommented:
No problem.  Glad it worked out for you.  The MapClassToEmp should work in the same way.

Happy Coding!

/kev
abernsAuthor Commented:
Sorry I wasn't clear...the query you provided didn't quite work, but generated an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID) INNER JO' at line 1

Do you want me to provide you some sample data to load?
UPDATE d2 SET d2.StartTime = d1.StartTime, d2.EndTime=d1.EndTime, d2.RoomNo=d1.RoomNo FROM (Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID) INNER JOIN (Classes c1 INNER JOIN ClassDetails d1 ON c1.ClassID = d1.ClassID) ON c2.SectionID = c1.SectionID AND c2.TermID = '420' AND c1.TermID = '410';

Open in new window

Kevin CrossChief Technology OfficerCommented:
Try this:
(You can change to a select statement to check first as well)
UPDATE d2 
SET d2.StartTime=d1.StartTime, d2.EndTime=d1.EndTime, d2.RoomNo=d1.RoomNo 
FROM Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID 
INNER JOIN Classes c1 ON c2.SectionID = c1.SectionID
INNER JOIN ClassDetails d1 ON c1.ClassID = d1.ClassID
WHERE c2.TermID = '420' AND c1.TermID = '410';

Open in new window

abernsAuthor Commented:
Unfortunately, I am still receiving an error: FROM Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID INNER JO' at line 3 ...does the order of the Inner Joins matter?
When I tried it as a Select query I waited a looooong time for a response from the server and finally just closed the browser.

Thanks! Let me know what other info I can provide.

Audg
Kevin CrossChief Technology OfficerCommented:
I am not sure what is wrong.  A sample of data would be great.  You can give the below a try in the meantime.
UPDATE d2 
SET d2.StartTime=tto.StartTime, d2.EndTime=tto.EndTime, d2.RoomNo=tto.RoomNo 
FROM Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID 
INNER JOIN (Select c1.SectionID, d1.StartTime, d1.EndTime, d1.RoomNo From Classes c1 INNER JOIN ClassDetails d1 ON c1.ClassID = d1.ClassID WHERE c1.TermID = '410') AS tto ON c2.SectionID = tto.SectionID
WHERE c2.TermID = '420';

Open in new window

abernsAuthor Commented:
I still get an error.
(FROM Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID INNER JO)
I will get back to you in a little while with some data to work with. Thanks a lot for your help!
Kevin CrossChief Technology OfficerCommented:
What keeps bothering me is the error message is cutting off the JOIN so not sure if something is limiting the length of the query OR if probably is at FROM.

Note that I don't have a parans in my code and one is showing up unless that is just part of your posting and not part of the error display.

Trying testing directly in MySQL query browser if not doing so already.
abernsAuthor Commented:
Hello,
I reran  in my query browser and got the following error (The parens in the previous post were mine, added just for the post.)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID INNER JOIN' at line 1

I'm posting a bit of corresponding data from the three tables in question; hopefully this will make things easier. Thanks again. p.s. Not in these cases, but many classes have more than one class detail record, which is why they're in a separate table, in case you are wondering.



CREATE TABLE `ClassDetails` (
  `DetailNo` mediumint(8) unsigned NOT NULL auto_increment,
  `ClassID` mediumint(8) unsigned NOT NULL default '0',
  `RoomNo` varchar(10) NOT NULL default 'TBA',
  `BuildingID` varchar(10) default NULL,
  `StartTime` time default '00:00:00',
  `EndTime` time NOT NULL default '00:00:00',
  `DayCode` varchar(10) NOT NULL default '0',
  `InstrType` char(1) NOT NULL default 'L',
  `LastModifiedCD` datetime NOT NULL default '0000-00-00 00:00:00',
  `DetailsLastModifiedBy` mediumint(6) NOT NULL default '553',
  PRIMARY KEY  (`DetailNo`),
  KEY `ClassID` (`ClassID`),
  KEY `RoomNo` (`RoomNo`),
  KEY `DayCode` (`DayCode`),
  KEY `DetailsLastModifiedBy` (`DetailsLastModifiedBy`)
) ENGINE=MyISAM AUTO_INCREMENT=9933034 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9933034 ;
 
 
INSERT INTO `ClassDetails` VALUES (1284, 1194, '3937', '0', 0x31373a33303a3030, 0x32313a33303a3030, 'M', 'L', '2006-04-25 12:20:09', 337);
INSERT INTO `ClassDetails` VALUES (1600, 1507, '1917', '0', 0x30383a33303a3030, 0x31303a33303a3030, 'M-Th', 'L', '2008-07-15 11:33:12', 95);
INSERT INTO `ClassDetails` VALUES (1693, 1600, '3937', '0', 0x31373a33303a3030, 0x32313a33303a3030, 'M', 'L', '2008-08-05 12:22:45', 2897);
INSERT INTO `ClassDetails` VALUES (1827, 1731, '3937', '0', 0x30393a30303a3030, 0x31333a30303a3030, 'Sat', 'L', '2006-07-17 12:04:22', 337);
INSERT INTO `ClassDetails` VALUES (1725, 1632, '2822', '0', 0x31373a33303a3030, 0x31393a33303a3030, 'M-Th', 'L', '2005-07-22 15:01:31', 95);
INSERT INTO `ClassDetails` VALUES (1726, 1633, '2822', '0', 0x31393a33303a3030, 0x32313a33303a3030, 'M-Th', 'L', '2005-07-22 15:02:13', 95);
INSERT INTO `ClassDetails` VALUES (3021, 2971, '3937', '0', 0x31373a33303a3030, 0x32313a33303a3030, 'M', 'L', '2006-11-01 11:25:38', 70);
INSERT INTO `ClassDetails` VALUES (3532, 3403, '3937', '0', 0x30393a30303a3030, 0x31333a30303a3030, 'Sat', 'L', '2006-10-16 19:00:56', 2637);
INSERT INTO `ClassDetails` VALUES (3053, 3003, '2822', '0', 0x31373a33303a3030, 0x31393a33303a3030, 'M-Th', 'L', '2005-09-25 21:50:00', 95);
INSERT INTO `ClassDetails` VALUES (3397, 3344, '2822', '0', 0x31393a33303a3030, 0x32313a33303a3030, 'M-Th', 'L', '2005-09-25 21:49:35', 95);
INSERT INTO `ClassDetails` VALUES (3016, 2966, '3963', '0', 0x31373a33303a3030, 0x32313a33303a3030, 'TTh', 'L', '2008-10-06 15:50:17', 95);
INSERT INTO `ClassDetails` VALUES (3276, 3223, '2134', '0', 0x30383a33303a3030, 0x31303a33303a3030, 'M-Th', 'L', '2004-09-29 20:00:00', 553);
 
 
CREATE TABLE `Classes` (
  `ClassID` mediumint(8) unsigned NOT NULL default '0',
  `STARS` varchar(10) default NULL,
  `CourseID` varchar(10) NOT NULL default '',
  `SectionID` varchar(10) NOT NULL default '',
  `ClassBeginDate` date default NULL,
  `ClassEndDate` date default NULL,
  `Suffix` varchar(4) default NULL,
  `SeatsTaken` smallint(5) NOT NULL default '0',
  `SeatsAvailable` smallint(5) default NULL,
  `ClassSize` smallint(5) default '35',
  `ClassMinimum` smallint(5) NOT NULL default '0',
  `CombinedClass` tinyint(4) NOT NULL default '0',
  `ShadowClass` tinyint(1) unsigned default '0',
  `Status` varchar(10) NOT NULL default 'OPEN',
  `Hold` tinyint(1) NOT NULL,
  `TermID` varchar(4) NOT NULL default '0',
  `ClassCommentsAbove` longtext,
  `TestRange` varchar(30) default NULL,
  `ClassCommentsBelow` longtext,
  `ClassListID` smallint(6) unsigned default NULL,
  `AENSPass` smallint(6) NOT NULL default '0',
  `AENSRP` smallint(6) NOT NULL default '0',
  `CPSbridge` tinyint(1) unsigned default '0',
  `Studio` tinyint(1) unsigned default '0',
  `Hybrid` tinyint(1) unsigned NOT NULL,
  `CDL` tinyint(1) NOT NULL,
  `LateStart` tinyint(1) NOT NULL,
  `DisplayDate` tinyint(1) NOT NULL,
  `LastModifiedCL` datetime NOT NULL default '0000-00-00 00:00:00',
  `ClassesLastModifiedBy` int(6) default '2897',
  `Display` tinyint(1) unsigned default '1',
  `fee` float(6,2) NOT NULL,
  `payout` varchar(25) NOT NULL default '',
  `contractSent` date NOT NULL default '0000-00-00',
  `contractSigned` date NOT NULL default '0000-00-00',
  `contractShipped` date NOT NULL default '0000-00-00',
  `ClassSort` tinyint(4) NOT NULL,
  PRIMARY KEY  (`ClassID`),
  KEY `CourseID` (`CourseID`,`SectionID`,`TermID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Dumping data for table `Classes`
-- 
INSERT INTO `Classes` VALUES (1194, '13625', '1800323', 'TRDR', 0x323030332d30352d3331, 0x323030332d30382d3136, '', 0, 10, 35, 0, 0, 0, 'OPEN', 0, '400', '', '', NULL, 4, 0, 0, 0, 0, 0, 0, 0, 0, '2006-04-25 12:20:09', 337, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (1600, '', '1800320', 'TRDR', 0x323030332d30392d3032, 0x323030332d31302d3235, '', 0, 0, 35, 0, 0, 0, 'OPEN', 0, '410', '', '', '', 4, 0, 0, 0, 0, 0, 0, 0, 0, '2008-08-05 12:22:45', 2897, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (2971, '51180', '1800321', 'TRDR', 0x323030342d31302d3235, 0x323030342d31322d3138, '', 11, 29, 35, 0, 0, 0, 'OPEN', 0, '420', '', '', '', 4, 0, 0, 0, 0, 0, 0, 0, 0, '2006-11-01 11:25:38', 70, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (2184, '44168', '1800320', 'TRDR', 0x323030342d30312d3132, 0x323030342d30332d3036, '', 1, 10, 35, 0, 0, 0, 'OPEN', 0, '450', '', '', '', 4, 0, 0, 0, 0, 0, 0, 0, 0, '2005-12-06 09:54:18', 70, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (3766, '44168', '1800321', 'TRDR', NULL, NULL, '', 1, 35, 35, 0, 0, 0, 'OPEN', 0, '460', '', '', '', 4, 0, 0, 0, 0, 0, 0, 0, 0, '2006-02-08 10:22:15', 95, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (2971, '51180', '1800321', 'TRDR', 0x323030342d31302d3235, 0x323030342d31322d3138, '', 11, 29, 35, 0, 0, 0, 'OPEN', 0, '420', '', '', '', 4, 0, 0, 0, 0, 0, 0, 0, 0, '2006-11-01 11:25:38', 70, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (2185, '44081', '1800320', 'TREE', 0x323030342d30312d3132, 0x323030342d30332d3036, '', 13, 4, 35, 0, 0, 0, 'OPEN', 0, '450', '', '', '', 1, 0, 0, 0, 0, 0, 0, 0, 0, '2007-01-04 21:11:56', 2637, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (3403, '51332', '1800321', 'TREE', 0x303030302d30302d3030, 0x303030302d30302d3030, '', 11, 3, 35, 0, 0, 0, 'OPEN', 0, '420', '', '', NULL, 1, 0, 0, 0, 0, 0, 0, 0, 0, '2006-10-16 19:00:56', 2637, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (3767, '44081', '1800321', 'TREE', NULL, NULL, '', 13, 35, 35, 0, 0, 0, 'OPEN', 0, '460', '', '', '', 1, 0, 0, 0, 0, 0, 0, 0, 0, '2006-02-06 15:57:54', 337, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (1349, '13630', '1800323', 'TREE', 0x323030332d30352d3237, 0x323030332d30382d3137, '', 0, 1, 35, 0, 0, 0, 'OPEN', 0, '400', '', '', NULL, 1, 0, 0, 0, 0, 0, 0, 0, 0, '2006-04-25 12:20:32', 337, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (1731, '', '1800320', 'TREE', 0x323030332d30392d3036, 0x323030332d31302d3235, '', 0, 0, 35, 0, 0, 0, 'OPEN', 0, '410', '', '', '', 1, 0, 0, 0, 0, 0, 0, 0, 0, '2006-07-17 12:04:21', 337, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (1632, '', '1800300', 'TR27', 0x323030332d30392d3032, 0x323030332d31302d3235, '', 0, 0, 35, 0, 0, 0, 'OPEN', 0, '410', '', '', '', 5, 0, 0, 0, 0, 0, 0, 0, 0, '2005-07-22 15:01:30', 95, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (1633, '', '1800300', 'TRVI', 0x323030332d30392d3032, 0x323030332d31302d3235, '', 0, 0, 35, 0, 0, 0, 'OPEN', 0, '410', '', '', '', 5, 0, 0, 0, 0, 0, 0, 0, 0, '2005-07-22 15:02:13', 95, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (3003, '48603', '1800300', 'TR27', 0x323030342d31302d3235, 0x323030342d31322d3138, '', 16, 15, 35, 0, 0, 0, 'OPEN', 0, '420', '', '', '', 5, 0, 0, 0, 0, 0, 0, 0, 0, '2005-09-25 21:50:00', 95, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
INSERT INTO `Classes` VALUES (3344, '51166', '1800313', 'TYVI', 0x323030342d31302d3235, 0x323030342d31322d3138, '', 7, 4, 35, 0, 0, 0, 'OPEN', 0, '420', '', '', '', 5, 0, 0, 0, 0, 0, 0, 0, 0, '2005-09-25 21:49:35', 95, 1, 0.00, '', 0x303030302d30302d3030, 0x303030302d30302d3030, 0x303030302d30302d3030, 0);
 
 
CREATE TABLE `MapClasstoEmp` (
  `ClassID` mediumint(6) unsigned NOT NULL default '0',
  `EmpID` mediumint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ClassID`,`EmpID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
INSERT INTO `MapClasstoEmp` VALUES (1600, 112);
INSERT INTO `MapClasstoEmp` VALUES (1731, 153);
INSERT INTO `MapClasstoEmp` VALUES (1632, 551);
INSERT INTO `MapClasstoEmp` VALUES (1633, 551);
INSERT INTO `MapClasstoEmp` VALUES (2971, 112);
INSERT INTO `MapClasstoEmp` VALUES (3403, 153);
INSERT INTO `MapClasstoEmp` VALUES (3003, 551);
INSERT INTO `MapClasstoEmp` VALUES (3344, 551);
INSERT INTO `MapClasstoEmp` VALUES (2966, 2856);
INSERT INTO `MapClasstoEmp` VALUES (3223, 503);

Open in new window

Kevin CrossChief Technology OfficerCommented:
Alright, just had to get my brain out of MS SQL Server and into MySQL:
UPDATE Classes c2 INNER JOIN ClassDetails d2 ON c2.ClassID = d2.ClassID
INNER JOIN (Select c1.SectionID, d1.StartTime, d1.EndTime, d1.RoomNo From Classes c1 INNER JOIN ClassDetails d1 ON c1.ClassID = d1.ClassID WHERE c1.TermID = '410') AS tto
ON c2.SectionID = tto.SectionID
SET d2.StartTime=tto.StartTime, d2.EndTime=tto.EndTime, d2.RoomNo=tto.RoomNo
WHERE c2.TermID = '420';

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
This just ran for, but you can verify better if the outcome is what you desired.  Then if that works, make the same change to the Map... query.
abernsAuthor Commented:
Awesome!  It worked! Thank you so much! Very impressive.
Kevin CrossChief Technology OfficerCommented:
Sorry it took so long.  Switching between syntax gets crazy sometimes.  I was managing a MySQL server Thursday/Friday then spent all day Sunday/Monday in SQL server. :)

Anyway, good luck and glad that helped.

Regards,
Kevin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.