CODEWITHSUNDEEP
mysqlsqlselectinsertwhere-clause
user3675304
user3675304

Reputation: 25

SQL INSERT INTO statement.. Error, I am very new to SQL

I've searched everywhere for an answer and can't find anyone so I have resorted to making my own post. This is for a university assignment. I am using HeidiSQL.

This is my code

INSERT INTO 
loan(patronCode,bookCode,loanDate,dueDate)
VALUES 
(
SELECT
    patron.patronCode
WHERE
    patron.fname='Jacob' AND patron.lname='Simmons', 
SELECT
    book.bookCode
WHERE
    book.bookTitle='The Agony And The Empathy',
'2014-09-10',
'2014-10-01'
),
(
SELECT
    patron.patronCode
WHERE
    patron.fname='Jacob' AND patron.lname='Simmons', 
SELECT
    book.bookCode
WHERE
    book.bookTitle='Kevin Rudd: The Biography',
'2014-09-10',
'2014-10-01'
)
FROM

loan
INNER JOIN patron 
  ON loan.patronCode=patron.patronCode
INNER JOIN book
 ON loan.bookCode=book.bookCode;

This is my error message:

SQL Error (1064): You have an error in your SQL synstax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT patron.patronCode WHERE patron.fname='Jacob' AND patron.lname='Si' at line 5

Upvotes: 1

Views: 183

Answers (2)

raj
raj

Reputation: 187

I believe the SELECT statement's syntax will be: 

SELECT patronCode FROM tablename WHERE ....

Upvotes: 0

crthompson
crthompson

Reputation: 15865

An insert statement needs to take the form of:

INSERT INTO TABLENAME(FIELDNAME1, FIELDNAME2...)
VALUES('HARDCODED_VALUE1', 'HARDCODED_VALUE2'...)

You have mixed this with the logic of an insert with a select statement. Which looks like this:

INSERT INTO TABLENAME(FIELDNAME1, FIELDNAME2...)
SELECT Column1, Column2...
FROM TABLENAME

You have 2 different inserts here. Perhaps they could be combined with a bit more information. Also, you dont indicate how patron and book are joined, probably they arent, so I've left the join out.

Here is the first one, the second can be patterned after it. 

INSERT INTO loan(patronCode,bookCode,loanDate,dueDate)
SELECT patron.patronCode,book.bookCode,'2014-09-10','2014-10-01'
FROM patron, book
WHERE
  patron.fname='Jacob' AND patron.lname='Simmons'
  and book.bookTitle='The Agony And The Empathy'

Upvotes: 1

Related Questions