0

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) :

DELIMITER //
CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 DATETIME)
BEGIN
  INSERT INTO `db`.`table1` (
        `col1`,
        `col2`,
        `col3`,
        `col4`,
        `col5`
      )
  SELECT val1, date1, date2, 1, `colA` FROM `table2` WHERE `colB` = val1;
END//
DELIMITER ;

But now I want to INSERT into a table (table1) an additional value (col6) SELECTed from a third different table (table3) upon which - as best as I can figure - there is nothing sensible to JOIN in one SELECT statement.

Something like :

DELIMITER //
CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 DATETIME, IN string1 VARCHAR(255))
BEGIN
  INSERT INTO `db`.`table1` (
        `col1`,
        `col2`,
        `col3`,
        `col4`,
        `col5`,
        `col6`
      ) 
  VALUES 
  (
    val1,
    date1,
    date2,
    1,
    (SELECT `colA` FROM `table2` WHERE `colB` = val1),
    (SELECT `colZ` FROM `table3` WHERE `colY` = string1)
  );
END//
DELIMITER ;

colA and colB from table2 are guaranteed UNIQUE within the table. Similarly, colZ and colY from table3 are guaranteed UNIQUE within the table.

val1/colB is PK in table2; colA is an FK to a fourth table (not important). colZ is PK in table3; string1/colY is not referenced anywhere else in the database.

In summary : col6 in table1 has an FK to table3's colZ. Hence, I want to INSERT into table1's col6 the INT of colZ based on a known unique, elsewhere unreferenced string in colY, while still being able to SELECT FROM table2 the FK value - colA - of a fourth unrelated table based on a given/known PK value colB and INSERT that into table1's col5.

How can I create a stored procedure that's basically the second example above ... in a form that MySQL will accept ?

(In case it's not super-clear from the example, col1 to col6 belong to table1; colA and colB belong to table2; colZ and colY belong to table3.)

  • Have you tried your code above, as it is? – ypercubeᵀᴹ Jun 15 '14 at 19:05
  • Your procedure would work without errors, as long as colB and colY are unique in the respective tables. The only problem is that if you provide a value (for either of these columns) that does not exist in the respective table, the subquery will produce a NULL which will be inserted into table1 (or the insert will fail if the col5/col6 is not nullable.) – ypercubeᵀᴹ Jun 15 '14 at 19:17

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.