Java SQL - Insert into table only new entries

Question

I am trying to insert new entries into my database, but only the new entries. If a class with the crn that I am adding already exists in the database then I would like to skip it to not have duplicates.

Below is the code I have right now. I have tried a few different methods but I keep getting exception:

java.sql.SQLSyntaxErrorException: 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 'EXCEPT crn' at line 1

The database entry works fine without the "EXCEPT crn", but again it adds duplicates.

try {
        String query = null;
        try {
            query = "INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?) EXCEPT crn";
        } catch(Exception e) {
            conn.close();
        }

        PreparedStatement preparedStmt = conn.prepareStatement(query);
        preparedStmt.setInt(1, crn);
        preparedStmt.setString(2, subject);
        preparedStmt.setInt(3, creditHours);
        preparedStmt.setString(4, title);
        preparedStmt.setString(5, capacity);
        preparedStmt.setString(6, instructor);
        preparedStmt.setString(7, schedule);
        preparedStmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }

GMB · Accepted Answer · 2020-04-06 21:18:08Z

1

If a class with the crn that I am adding already exists in the database then I would like to skip it to not have duplicates.

In MySQL, I would recommend the insert ... on duplicate key syntax:

INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE crn = VALUES(crn);

For this to work, you need a unique constraint (or the like) on column crn:

ALTER TABLE Classes ADD CONSTRAINT cs_classes_uniq_crn UNIQUE(crn);

Then, when an INSERT occurs that would generate a duplicate crn, the query goes to the UPDATE clause, that actually performs a no-op.

answered Apr 6, 2020 at 21:18

GMB

224k25 gold badges103 silver badges151 bronze badges

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

3 Comments

HomeIsWhereThePcIs Over a year ago

Or INSERT IGNORE if he does not want to update existing records

GMB Over a year ago

@HomeIsWhereThePcIs: I am not a big fan of INSERT IGNORE, becausei it silently ignores any error that occurs during the operation (like invalid data for example). ON DUPLICATE KEY gives much more control.

stackiee Over a year ago

I was actually using INSERT IGNORE but it wasn't working. Looks like it was because i didn't specify crn to be a unique constraint. Thanks!

Glahf · Accepted Answer · 2020-04-06 21:28:04Z

0

Can you alter the row or rows you want? You could just put a unique constraint on them so they can't accept columns that have the same value.

ALTER TABLE table_name ADD UNIQUE (column_name);

If you need multiple columns you can add the constraint to the table like this: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, etc);

answered Apr 6, 2020 at 21:28

Glahf

1411 silver badge7 bronze badges

Comments

forpas · Accepted Answer · 2020-04-06 21:57:27Z

The SQL statement that you try to execute is invalid because MySql does not support EXCEPT.
Since you want to skip the insertion of rows that already exist you can use INSERT IGNORE:

query = "INSERT IGNORE INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
        + "VALUES (?, ?, ?, ?, ?, ?, ?)";

but for this to work there should be a unique constraint for the column crn.
It seems like it is the PRIMARY KEY of the table so it is already unique.
If there isn't a unique constraint for the column crn you can use NOT EXISTS like this:

query = "INSERT INTO Classes (crn, subject, creditHours, title, capacity, instructor, schedule) "
        + "SELECT ?, ?, ?, ?, ?, ?, ? "
        + "WHERE NOT EXISTS (SELECT 1 FROM Classes WHERE crn = ?)";

so you will have to pass as the 8th parameter of the Prepared Statement crn again:

preparedStmt.setInt(8, crn);

Collectives™ on Stack Overflow

Java SQL - Insert into table only new entries

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related