Mysql update if exist insert if not exist

Question

Hello i have a database named "distribuzioni" and it is composed in this way:

stmt.executeUpdate("CREATE TABLE distribuzioni " +
                "(id INTEGER primary key auto_increment, " +
                "nazione TEXT NOT NULL, " +
                "categoria TEXT NOT NULL, " +
                "risultato TEXT NOT NULL, " +
                "densita FLOAT(3,2))");

Now, suppose I have this four parameter in my Java project:

String nation="USA";
String category="First Division";
String result="2-1";
float density=0.34;

Now i want update the parameter densita=density of my distribuzioni DB if it have a row that contains nazione=nation, categoria=category, risultato=result.

If my database not contain a row with nazione=nation, categoria=category, risultato=result i want create it with densita=density with an INSERT query.

Thanks

Possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — maraca, Nov 15 '15 at 19:35
I think this is not my case because i can't modify the primaryKey — aRaKaNoiD, Nov 15 '15 at 20:06

score 2 · Accepted Answer · answered Nov 16 '15 at 09:05

First I must say you keep saying database. You mean table.

Schema

CREATE TABLE distribuzioni
(   id int primary key auto_increment,
    nazione varchar(100) NOT NULL,
    categoria varchar(100) NOT NULL,
    risultato varchar(100) NOT NULL,
    densita FLOAT(3,2),
    unique key(nazione,categoria,risultato) -- this is important
);

Let's start with some data in our table.

insert distribuzioni(nazione,categoria,risultato,densita)
values ('Italy','fish','thing1',1.23);

select * from distribuzioni;
+----+---------+-----------+-----------+---------+
| id | nazione | categoria | risultato | densita |
+----+---------+-----------+-----------+---------+
|  1 | Italy   | fish      | thing1    |    1.23 |
+----+---------+-----------+-----------+---------+

That was previous data above. A starting point.

Below is how new data goes in as you want:

insert distribuzioni(nazione,categoria,risultato,densita)
values ('France','cars','thing8',1.11) 
on duplicate key update densita=1.11;

insert distribuzioni(nazione,categoria,risultato,densita)
values ('Italy','fish','thi1ng1',5.11) 
on duplicate key update densita=5.55;

.

select * from distribuzioni;
+----+---------+-----------+-----------+---------+
| id | nazione | categoria | risultato | densita |
+----+---------+-----------+-----------+---------+
|  1 | Italy   | fish      | thing1    |    5.55 |
|  2 | France  | cars      | thing8    |    1.11 |
+----+---------+-----------+-----------+---------+

Important point. If you do not make the unique composite key on those three columns, the update will not fire off and you will get another row that you do not want.

Also, and I am just saying this to save you the suspense, each time the update triggers, even though you get the update you want, it will increment internally to the table (not in a row) the next available auto inc number. So that is a side-effect. Try it out, you will see what I mean. Your data will be fine, but you will get gaps.

Here is the manual page on Insert on Duplicate Key Update

Remember, without the unique composite key, this will not work. So do not waste your time on it, without it. What is a unique composite key you ask? It is the line in the Schema shown that says next to it -- this is important. And it means that there can only be one row in that table with a combination of those three values as a combination. That is the magic that makes it insert or update.

sounds good. Do that. :) Your mysql server will be pleased. – Drew Nov 16 '15 at 09:23 — Drew, Nov 16 '15 at 09:23
I am glad to hear that. Good luck with your projects :) – Drew Nov 16 '15 at 15:48 — Drew, Nov 16 '15 at 15:48

score 0 · Answer 2 · answered Nov 15 '15 at 20:11

0

One possible way would be to create 3 link tables for nazione, categoria, risultato, making their primary id an index of your distribuzioni table and use ON DUPLICATE KEY UPDATE when you make an insert on it.

answered Nov 15 '15 at 20:11

TGrif

4,873
8
27
44

score 0 · Answer 3 · edited May 23 '17 at 11:59

As @maraca said go to look How to 'insert if not exists' in MySQL?

Quote :

Method 1: using REPLACE

It’s very simple:
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;
If the record exists, it will be overwritten; if it does not yet exist, it will be created. However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just to skip them.

As you get auto_inc ID; just don't put it in the SET

Mysql update if exist insert if not exist

3 Answers3

Schema

Method 1: using REPLACE