CODEWITHSUNDEEP
javamysqlsqlinsertprepared-statement
James McTyre
James McTyre

Reputation: 103

Check if similar row with same ID is already inserted into table?

I have a Car table with columns carID, brand, color, price. I'm copying carID and color to another table Detail which have columns carID, finish, color (with carID being a foreign key from Car table and the finish column is not NULL).

I have an SQL Prepared Statement:

public void insertToDetail(int carID, String finish){

     String sql = "INSERT INTO detail (carID, finish, color) SELECT ?, ?, color FROM car WHERE carID = ?;";

     PreparedStatement psmt = connect.prepareStatement(sql);

     psmt.setString(1, carID);
     psmt.setInt(2, finish);
     psmt.setInt(3, carID);
     psmt.executeUpdate();  
     psmt.close();
}

How may I check if an exact same carID value is not already inserted into my Detail table?

I tried something like:

"INSERT INTO detail (carID, finish, color) 
SELECT ?, ?, color FROM car 
WHERE NOT EXISTS {
     SELECT carID 
     FROM detail 
     WHERE carID = ?
 } ;";

And it gave me the error:

MySQLSyntaxError 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 '{ SELECT carID FROM detail WHERE carID = 123 }' at line 1

Upvotes: 0

Views: 376

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

The query you are trying to write is:

INSERT INTO detail (carID, finish, color) 
    SELECT ?, ?, color
    FROM car 
    WHERE NOT EXISTS (SELECT carID 
                      FROM detail 
                      WHERE carID = ?
                     ) ;

However, NOT EXISTS is the wrong approach. Let the database do the checking. So create a unique index:

CREATE UNIQUE INDEX unq_detail_carid on detail(carID);

This will generate duplicate car ids in the table. An insert will return an error if a duplicate is generated.

To prevent the error, use ON DUPLICATE KEY UPDATE:

INSERT INTO detail (carID, finish, color) 
    SELECT ?, ?, color
    FROM car 
    ON DUPLICATE KEY UPDATE carID = VALUES(carID);

Upvotes: 1

flyingfox
flyingfox

Reputation: 13506

Your sql have syntax error,need to change {} to () and remove ; at the end

so change

"INSERT INTO detail (carID, finish, color) SELECT ?, ?, color FROM car WHERE NOT EXISTS {
SELECT carID FROM detail WHERE carID = ?;";

to

"INSERT INTO detail (carID, finish, color) SELECT ?, ?, color FROM car WHERE NOT EXISTS (
SELECT carID, finish, color FROM detail WHERE carID = ?)";

Upvotes: 1

Related Questions