0

EDIT: I want to replace Appointments.AppointmentTime with tempAppointmentTime.appointmentTime where AppointmentDate = '2022-01-01'. Please see the SQL script at the end to generate the 2 tables.

I have 2 tables: Appointments and tempAppointmentTime, they look like this:

Appointments:

enter image description here

tempAppointmentTime:

enter image description here

I'd like to copy the appointmentTime of tempAppointmentTime and paste to AppointmentTime of Appointments where AppointmentDate = '2022-01-01'. Both of them have 9 rows. I tried many solutions from this post: mysql update column with value from another table

UPDATE Appointments A INNER JOIN tempAppointmentTime T ON A.AppointmentDate = '2022-01-01' SET A.AppointmentTime = T.appointmentTime;

UPDATE Appointments SET Appointments.AppointmentTime = 
    (SELECT tempAppointmentTime.appointmentTime FROM tempAppointmentTime WHERE tempAppointmentTime.appointmentDate = '2022-01-01');

UPDATE tempAppointmentTime T, Appointments A SET A.AppointmentTime = T.appointmentTime WHERE A.AppointmentDate = '2022-01-01';

None of them worked. Any idea how I can do this?

You can run this script to generate the 2 tables:

DROP TABLE IF EXISTS Appointments;

CREATE TABLE `Appointments` (
    `ID` VARCHAR(36) NOT NULL UNIQUE,
    `AppointmentDate` DATE,
    `AppointmentTime` TIME
);

DROP TABLE IF EXISTS tempAppointmentTime;

CREATE TABLE `tempAppointmentTime` (
    `ID` VARCHAR(36) NOT NULL UNIQUE,
    `appointmentTime` TIME,
    `appointmentDate` DATE,
    CONSTRAINT `PK_tempAppointmentTime` PRIMARY KEY (`ID`)
);

DROP PROCEDURE IF EXISTS insertAppointments;
DELIMITER //
CREATE PROCEDURE insertAppointments()
BEGIN
    DECLARE i INT;
    SET i = 0;
    loop_label: LOOP
        IF i > 8 THEN
            LEAVE loop_label;
        END IF;
        INSERT INTO Appointments VALUES(UUID(), '2022-01-01', '08:00:00');
        SET i = i + 1;
        ITERATE loop_label;
    END LOOP loop_label;
    SET i = 0;
    loop_label: LOOP
        IF i > 3 THEN
            LEAVE loop_label;
        END IF;
        INSERT INTO Appointments VALUES(UUID(), '2022-01-02', '08:00:00');
        SET i = i + 1;
        ITERATE loop_label;
    END LOOP loop_label;
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS insertTempTime;
DELIMITER //
CREATE PROCEDURE insertTempTime()
BEGIN
    DECLARE iterateTimes INT;
    DECLARE i INT;
    DECLARE totalRows INT;
    DECLARE insertTime TIME;
    SET iterateTimes = 3;
    SET totalRows = 8;
    SET i = 0;
    SET insertTime = '08:00:00';
    loop_label: LOOP
        IF i > totalRows THEN
            LEAVE loop_label;
        END IF;
        INSERT INTO tempAppointmentTime VALUES (UUID(), insertTime, '2022-01-01');
        SET insertTime = AddTime(insertTime, '00:20:00');
        SET i = i + 1;
        IF insertTime = '09:00:00' THEN 
            SET insertTime = '08:00:00';
        END IF;
        ITERATE loop_label;
    END LOOP loop_label;
END //
DELIMITER ;

CALL insertAppointments;
CALL insertTempTime;
one-hand-octopus
  • 2,229
  • 1
  • 17
  • 51
  • Do you want insert new entries in another table picking some values from first table? – Ankit Jindal May 20 '22 at 10:38
  • @AnkitJindal no I'd like to replace existing values of first table with values from 2nd table. – one-hand-octopus May 20 '22 at 10:41
  • There is no relationship between these 2 tables so allocate row numbers to both then update join. And do publish sample data as text which can be used , I have no intention of transcribing to provide a working answer. – P.Salmon May 20 '22 at 10:41
  • @P.Salmon I added the script to generate the 2 tables. – one-hand-octopus May 20 '22 at 10:52
  • @P.Salmon why is there no relationship? The date for both tables are '2022-01-01'? – one-hand-octopus May 20 '22 at 10:53
  • That's not enough to establish which row in a should be updated from which row in b. – P.Salmon May 20 '22 at 10:58
  • @P.Salmon I simply want to replace the first 9 rows in `Appointments.AppointmentTime` with `tempAppointmentTime.appointmentTime` – one-hand-octopus May 20 '22 at 11:01
  • And there's the problem there is no first - data is not stored in any particular order. However if you had chosen to have an auto_increment column first (and order) could have been established. – P.Salmon May 20 '22 at 11:02
  • @P.Salmon can you show me how to use row numbers to achieve my goal? As long as the pasted data follows the order '08:00:00, 08:20:00, 08:40:00, 09:00:00, 08:00:00, 08:20:00, 08:40:00, 09:00:00, 08:00:00' – one-hand-octopus May 20 '22 at 11:06

0 Answers0