CODEWITHSUNDEEP
mysqlsql
Aditya Singh
Aditya Singh

Reputation: 852

MySQL update if exists

I have been trying to do insert / update records in a mysql table. Cannot use ON DUPLICATE KEY because i have nothing to do with the primary key.

Basically i have to update a record in the database 

INSERT INTO table (city, state, gender, value) VALUES ("delhi","delhi","M",22)

If a record of that city, state, gender exists, then simply overwrite the value.

Can i achieve this without sending two queries from the programming language

Upvotes: 2

Views: 3988

Answers (2)

Ja͢ck
Ja͢ck

Reputation: 173582

Keep in mind that constructs such as ON DUPLICATE KEY and REPLACE INTO were specifically designed to prevent exactly that. The only other way to prevent two queries from your application layer is by declaring a database function that does the same things.

Therefore, add either a UNIQUE(city, state, gender) key or a primary key that spans the same columns. The difference between the two lies in the value range of each column; primary keys force NOT NULL whereas UNIQUE allows for columns to be NULL.

The difference is subtle but can sometimes lead to unexpected results, because NULL values are considered to be unique. For example, let's say you have this data in your database:

nr  | name
123 | NULL

If you try to insert another (123, NULL) it will not complain when you use UNIQUE(nr,name); this may seem like a bug, but it's not.

Upvotes: 3

John Woo
John Woo

Reputation: 263733

actually you can still use ON DUPLICATE KEY, just add a unique index on the following columns, eg

ALTER TABLE tbl_name ADD UNIQUE index_name (city, state, gender)

your query now will be,

INSERT INTO table (city, state, gender, value) 
VALUES ('delhi','delhi','M', 22)
ON DUPLICATE KEY UPDATE value = 22

Upvotes: 4

Related Questions