Avoid duplicates in INSERT INTO in the same table

user_11177543 0
Viewed 1393

I have a program in JAVA which creates a table in a database and then I insert rows in this table. The table is created as below:

        String sql = "CREATE TABLE IF NOT EXISTS weather (\n"
            + " city string,\n"
            + " temp real,\n"
            + " feels_like real,\n"
            + " temp_min real,\n"
            + " temp_max real,\n"
            + " pressure integer,\n"
            + " humidity integer\n"
            + ");";

When I add rows, I don't want to have duplicates for the field named "city". So, for example, if I already have London with its data, I don't want to add it again, even though that all its data may have changed. I want to have it only once in my table.

I have this query for insertion :

        String sql = "INSERT INTO weather VALUES(?,?,?,?,?,?,?);";

and I want to modify it so I don't insert city duplicates. Can anyone help me please? Thanks!

duplicates upsert sqlite sql java
3 Answers

If the version of SQLite you use is 3.24.0+ and there is a unique constraint for the column city, you can use upsert which gives you an option to do NOTHING or UPDATE the table if a unique constraint violation occurs.

In this case:

String sql = 
    "INSERT INTO weather VALUES(?,?,?,?,?,?,?) " +
    "ON CONFLICT DO NOTHING";

if you try to insert a row with an existing city, the statement will fail without an error.

But if the new row contains up to date data for the other columns and you want the row updated, you can do this:

String sql = 
    "INSERT INTO weather VALUES(?,?,?,?,?,?,?) " +
    "ON CONFLICT(city) DO UPDATE SET "
    "temp = excluded.temp, " + 
    "feels_like = excluded.feels_like, " + 
    "temp_min = excluded.temp_min, " + 
    "temp_max = excluded.temp_max, " + 
    "pressure = excluded.pressure, " + 
    "humidity = excluded.humidity";

and the other 6 columns will be overwritten by the new values you supplied.

If there isn't a unique constraint defined for city and you don't want to or can't define one, then you can avoid inserting the same city twice with NOT EXISTS like this:

String sql = 
    "INSERT INTO weather SELECT ?,?,?,?,?,?,? " +
    "WHERE NOT EXISTS (SELECT 1 FROM weather WHERE city = ?);

In this case you will have to pass in your Java code as an additional 8th parameter the value of the city again.

user_104988280

There is no way to do this in 'effectively standard SQL'*.

But, each individual DB engine does usually have some way of accomplishing this goal.

The concept is called merging or upserting - those are terms you can search the web for. Just search for 'how to postgres upsert' for example. It's called upsert because the more general application is: If some subset of the values I am inserting doesn't exist yet in the DB, INSERT a new row with this data. Otherwise, find the row with the same values for this subset, and then update all the other values with it. For example: "Find the student with ID 12345, and then change the name to 'Joe Bloggs'. If there is no row with that, then make it".

make ALL the values 'the key' and you've reduced your 'insert, ignore if it is already there' to a standard UPSERT.

In psql, you can do ON CONFLICT. searching for 'mysql upsert' gets you to blog posts that give you varying tactics depending on your exact needs, from using INSERT IGNORE (I advice against this, this ignores any and all errors, vs only ignoring the 'already in here' error only), ON DUPLICATE KEY UPDATE (better idea, this), or using REPLACE.

Similar blog posts will be found for any db engine you are using here.

*) Defined not as 'as per some version of the SQL standard', but defined as: 'works in the majority of existing DB engines'.

user_7686440

You can now use on conflict. This requires that you have a unique index/constraint on city but it is already defined as the primary key. Check.

insert into weather ( . . . )
    values ( . . . )
    on conflict (city) ignore;

SQLite also allows this shorthand:

insert or ignore into weather ( . . . )
    values ( . . . );
user_11440350
Related