Mysql – wrong with this thesql INSERT statement

foreign keyinsertMySQL

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
    )
);

Best Answer

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.

Related Question