Get error when trying to insert data into MySQL

Question

I'm using below code to do checking before data get inserted into MySQL. However, I get error as below

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM movie_title WHERE title = 'ABC')' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

Here my code

public void checkAndAdd(String movieTitle) throws Exception {
        // TODO Auto-generated method stub
        DatabaseConnection db=new DatabaseConnection();
        Connection connect=db.getConnection();
        String sql="INSERT INTO movie_title(title) VALUES (?) WHERE NOT EXISTS (SELECT * FROM movie_title WHERE title = ?)";
        PreparedStatement ps=connect.prepareStatement(sql);
        ps.setString(1,movieTitle);
        ps.setString(2,movieTitle);
        ps.executeUpdate();
        connect.close();
        ps.close(); 

    }

Edited

String sql=" IF NOT EXISTS(SELECT * FROM movie_title WHERE title = ?) INSERT INTO movie_title (title) VALUES (?) ";

How to find your problem: take the SQL that you are executing in Java, copy it into mysql and run it. When mysql gives you an error, debug your SQL — Don Cheadle
– Don Cheadle, Commented May 12, 2016 at 15:40

score 4 · Accepted Answer · 2016-05-12 15:45:09Z

4

You can't put a WHERE clause in your SQL when you're using the VALUES keyword. Try something like this:

IF NOT EXISTS(SELECT 1 FROM movie_title WHERE title = ?)
    INSERT INTO movie_title (title)
    VALUES (?)

I work with SQL Server mostly, so I'm not sure if that's completely valid for MySQL, but it will be something similar.

Okay, for MySQL it has to be a little different:

INSERT INTO movie_title (title)
SELECT mt.title
FROM
    (SELECT ? title) mt
WHERE NOT EXISTS (
    SELECT 1 FROM movie_title WHERE title = ?)

edited May 12, 2016 at 15:45

answered May 12, 2016 at 15:34

user5886152

Sign up to request clarification or add additional context in comments.

6 Comments

John Joe Over a year ago

you mean

String sql="IF NOT EXISTS(SELECT 1 FROM movie_title WHERE title = ?)     INSERT INTO movie_title (title)     VALUES (?)";

?

user5886152 Over a year ago

Yes, I just edited the query itself, but you can put it in a string like that.

John Joe Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS(SELECT * FROM movie_title WHERE title = '1') INSERT INTO movie_tit' at line 1

John Joe Over a year ago

the movieTitle parameter holds 1. What should I change in this line (SELECT 1 FROM movie_title WHERE title = ?)

user5886152 Over a year ago

Like mmcrae said above, try it directly in MySQL and debug from there. The SELECT 1 should be fine, I'm just not completely sure how to use the ? for parameters. Replace the ? with actual values and test it that way first.

|

Collectives™ on Stack Overflow

Get error when trying to insert data into MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related