2

Massive edit to follow procedure and enable reproducing this in a fiddler somewhere.

Table 1, schedule:

CREATE TABLE `schedule` (
  `scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sessionid` int(11) NOT NULL DEFAULT '0',
  `roomid` int(11) NOT NULL DEFAULT '0',
  `starttime` time NOT NULL DEFAULT '00:00',
  `schorder` int(11) DEFAULT NUL
);

Sample data:

INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (1, 13, 2, '18:00');
INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (2, 17, 3, '19:00');
INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (3, 18, 2, '19:00');
INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (4, 23, 2, '17:00');

Table 2, rooms:

CREATE TABLE `rooms` (
  `roomid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sort_order` int(11) NOT NULL DEFAULT '0'
);

Sample data:

INSERT INTO `rooms` (roomid, sort_order) VALUES (2, 1);
INSERT INTO `rooms` (roomid, sort_order) VALUES (3, 2);

The SELECT statement that gets me the schedule in order by start time, then room sort order:

SELECT
    SCH.starttime,
    R.roomid,
    SCH.sessionid
    FROM
         schedule SCH
    JOIN rooms R USING (roomid)
    ORDER BY
        SCH.starttime, R.sort_order;

The result of that SELECT statement, which is accurate:

┌─────────────┬────────┬───────────┐
│SCH.starttime│R.roomid│S.sessionid│
├─────────────┼────────┼───────────┤
│17:00        │2       │23         │
│18:00        │2       │13         │
│19:00        │2       │18         │
│19:00        │3       │17         │
└─────────────┴────────┴───────────┘

What I want to do is use that result to update the sch_order field in the schedule table with an incrementing number.

Is that possible? If so, how would I do it?

Here's the SQL Fiddle for the above.

[ETA] Complicating factor: I'm on a hosted server, and I don't have temporary table privileges.

  • Can you please give us your table structures as DDL ('CREATE TABLE blah (...);) and your table data in the form of DML (INSERT INTO blah VALUES (...);`). Help us to help you. There are a few articles on how to ask questions here on my profile - you might want to take a look? p.s. welcome to the forum! :-) – Vérace Jun 9 at 19:12
  • Sure thing! Thanks for the pointer; I'll go edit it now. – XMS Jun 9 at 19:17
  • Show us the SELECT that will deliver the rows in the desired order, then we can tell you how to tack on an "incrementing number". – Rick James Jun 9 at 20:00
  • I thought I had? The "SQL that gets me the schedule" portion of the question. Updated just in case! – XMS Jun 9 at 21:47
  • See MyISAM's 2-column PRIMARY KEY. – Rick James Jun 10 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.