Question 207696 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to insert two values in a many-to-many relationship from a python SQL query?

I am trying to insert authors in an authors table to match sources through an association table of author_ids and source_ids in a flask website. I am using mariaDB, therefore I am aiming to have it all in one SQL query. So far I haven’t been able to make it work multiple queries.

INSERT INTO author (forename,surname) VALUES ("Bob","Smith"),("Jackie", "Brown");
INSERT INTO source (title) VALUES ("Short Stories");
INSERT INTO associate_author (author_id,source_id) VALUES 
(select LAST_INSERT_ID() from author, select LAST_INSERT_ID() from source);
(SELECT (MAX(id)-1) FROM author, SELECT (MAX(id) FROM source);

How should this query look, or how to fix my above queries to insert into many-to-many relationships?

The long way round with pull variables into python is as below.

INSERT INTO author (forename,surname) VALUES ("Bob","Smith");
INSERT INTO source (title) VALUES ("Short Stories");
var a =  (MAX(id) FROM from author;
INSERT INTO author (forename,surname) VALUES ("Jackie","Smith");
var b = select (MAX(id) FROM from author;
var c = SELECT (MAX(id) FROM source)
INSERT INTO associate_author (author_id,source_id) VALUES 
(a, c);
INSERT INTO associate_author (author_id,source_id) VALUES 
(b, c);
Tags:

2

Answers


  1. 0

    This is one method, but you need to go for every author make a query in a loop

    mysql supports user defined variable that are valid in a session.

    This must be done as MySQL doesn’t support RETURNING

    CREATE tABLE author (id int auto_increment primary key, forename varchar(10),surname varchar(10))

    CREATE tABLE source (id int auto_increment primary key, title varchar(30))

    CREATE tABLE associate_author (author_id int,source_id int)

    BEGIN;
INSERT INTO source (title) VALUES ("Short Stories");
SET @title = (select LAST_INSERT_ID() from source);

INSERT INTO author (forename,surname) VALUES ("Bob","Smith");
INSERT INTO associate_author (author_id,source_id)  
SELECT 
  (select LAST_INSERT_ID() id from author LIMIT 1) , @title title;

INSERT INTO author (forename,surname) VALUES ("Jackie", "Brown");
INSERT INTO associate_author (author_id,source_id)  
SELECT 
  (select LAST_INSERT_ID() id from author LIMIT 1) , @title title;
COMMIT;

    Records: 1  Duplicates: 0  Warnings: 0

    Records: 1  Duplicates: 0  Warnings: 0

    SELECT * FROM associate_author
    author_id source_id
    1 1
    2 1

    fiddle

    A more elegant solution would to use triggers, but you need still catch the source id first.

    Please observer that dbfiddle desn’t need DELIMITER for creating a trigger, so depending on gui you need to add it

    CREATE tABLE author (id int auto_increment primary key, forename varchar(10),surname varchar(10))

    CREATE tABLE source (id int auto_increment primary key, title varchar(30))

    CREATE tABLE associate_author (author_id int,source_id int)

    CREATE TRIGGER after_author_insert
AFTER INSERT
ON author FOR EACH ROW
BEGIN
INSERT INTO associate_author (author_id,source_id)  
SELECT 
  (NEW.id) , @title ;
END

    BEGIN;
INSERT INTO source (title) VALUES ("Short Stories");
SET @title = (select LAST_INSERT_ID() from source);

INSERT INTO author (forename,surname) VALUES ("Bob","Smith"), ("Jackie", "Brown");

COMMIT;

    Records: 2  Duplicates: 0  Warnings: 0

    SELECT * FROM associate_author
    author_id source_id
    1 1
    2 1

    fiddle

    Login or Signup to reply.
  2. 0

    For MariaDB only:

    Keep it simple and avoid additional selects, triggers and other overhead. Especially SELECT max(id) might give you an invalid result, since another connection could have inserted and committed another row between.

    Instead use RETURNING:

    INSERT INTO author (forename,surname) VALUES ("Bob","Smith") RETURNING @a_id=author_id;
INSERT INTO source (title) VALUES ("Short Stories") RETURNING @s_id=source_id;
INSERT INTO associate_author (author_id, source_id) VALUES (@a_id, @s_id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search