What is wrong with this mysql INSERT statement?

Question

I have table:

Book:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price: (decimal) not null

and

Artical:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price: (decimal) not null,
- book_id:(int) foreign key referencing book

I already have couple of books in Book table while Artical table is empty, i want to have and insert statement in Artical that will insert data and fill in book_id foreign key properly. I came up to this statement wich is not good but i'm feeling i'm close, here it is:

INSERT INTO Artical (id,title,price,book_id) 
VALUES (
 null,
 'Six faces of shadow',
 15, 
 ( 
 SELECT book.id 
 FROM book 
 JOIN Artical AS ar 
 WHERE book.title = ar.title
 )
);

• Akina: You cannot use correlated subquery because of potential iteration - correlational selection may theoretically change correlation condition and alter the rowset returned by correlated subquery.

Answer 1

The correct syntax is either to use just values or a query, not a mix of both.

You can however just insert values in your query.

INSERT INTO Artical
(title, price, book_id)
SELECT 
'Six faces of shadow',
15,
book.id
FROM book 
JOIN Artical AS ar ON book.id = ar.book_id

Also note, that you shouldn't join on the title column, especially since you already have a foreign key relation between the two tables.

You can insert NULL into a auto_increment column, but you can also just leave it out.

And as final but probably most important hint: Your tables are not properly normalized. What is the table Artical even for? title and price already are in the Book table. You should reconsider that. But that's not in the scope of this question.

Answer 2

There are two way to insert data:

1. if you have to insert data in Artical table with specific book title then do following

INSERT 
INTO Artical (title, price, book_id) 
VALUES (
 'Six faces of shadow', 
 15, 
 (
 SELECT book_id FROM book 
 WHERE book.title = "Six faces of shadow"
 )
);

2. if you want to add all book title with id then do following

INSERT 
INTO Artical (title, price, book_id) 
SELECT title, price, book_id FROM book

Hope this Help!!!