Photoshop Question posted in
The official user guide can be found here.

Error Code 1172 in procedures to insert data – Photoshop

I have three tables students [id (PK, NN, AI), name], courses [id (PK, NN, AI), name] and studentCourses [students.id (PK, FK), courses.id (PK, FK)]

I’m trying to code the example of this video in mysql:
https://www.youtube.com/watch?v=cEfjYynwooE&list=PLfh-ZUr1u03oCF23J3YfWSyLT_NJ0nGA4&index=29&t=81s

This is my code

DELIMITER //
CREATE PROCEDURE insertData(IN studentName_param varchar(45), IN courseName_param varchar(45))
BEGIN

    SET @StudentId = '';
    SET @CourseId = '';

    SELECT COALESCE(Id, 0) INTO @StudentId FROM Students WHERE StudentName = studentName_param;
    IF @StudentId = 0 THEN INSERT INTO Students (StudentName) VALUES (studentName_param);

    SELECT LAST_INSERT_ID() INTO @StudentId FROM Students;
    END IF;
    

    SELECT COALESCE(Id, 0) INTO @CourseId FROM Courses WHERE CourseName = courseName_param;
    IF @CourseId = 0 THEN INSERT INTO Courses (CourseName) VALUES (courseName_param);

    SELECT LAST_INSERT_ID() INTO @CourseId FROM Courses;
    END IF;
      

    INSERT INTO StudentCourses (StudentID, CoursesID) VALUES (@StudentId, @CourseId);
    
END //
DELIMITER ;

CALL insertData ('Joe', 'Photoshop') //no problem
CALL insertData ('Joe', 'Actionscript') //Error Code: 1172. Result consisted of more than one row

Any idea? Any help?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks Akina.

    Solution totally different but it works fine.

    I could solved my solution:

    1. StudentName and CourseName, defined as unique as you suggested

    2. I have changed 'Last_Insert_ID' by 'MAX(Id)'.

    i know that my solution is not perfect, i'll try to do best just for fun

    THANK YOU ALL


  2. 0

    The scheme needs StudentName and CourseName columns to be defined as UNIQUE in according tables (if not there is no way to distinguish rows with equal values). If not then fix this (do it once) by

    CREATE UNIQUE INDEX idx ON Students (StudentName);
CREATE UNIQUE INDEX idx ON Courses (CourseName);

    Then:

    1. Insert StudentName and CourseName into according tables unconditionally using INSERT IGNORE
    INSERT IGNORE INTO Students (StudentName) VALUES (studentName_param);
INSERT IGNORE INTO Courses (CourseName) VALUES (courseName_param);
    1. Insert data into link table using
    INSERT INTO StudentCourses (StudentID, CoursesID)
SELECT s.id, c.id
FROM Students s
CROSS JOIN Courses c
WHERE s.StudentName = studentName_param
  AND c.CourseName = courseName_param;

    You may combine this into one SP:

    DELIMITER //;
CREATE PROCEDURE insertData( IN studentName_param varchar(45), 
                             IN courseName_param varchar(45) )
BEGIN
    INSERT IGNORE INTO Students (StudentName) VALUES (studentName_param);
    INSERT IGNORE INTO Courses (CourseName) VALUES (courseName_param);
    INSERT INTO StudentCourses (StudentID, CoursesID)
        SELECT s.id, c.id
        FROM Students s
        CROSS JOIN Courses c
        WHERE s.StudentName = studentName_param
          AND c.CourseName = courseName_param;
END//;
DELIMITER ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search