Is it possible to make an insert or an update in the same sql query?

Question

I have a question that seems easy but i failed to solve it by miself

i have this table with the statistics of a website visitors

+-----------------------------+
+ date        | visits        +
+-----------------------------+
+  2014-03-17 | 198           +
+  2014-03-18 | 259           +
+  2014-03-19 | 94            +                 
+-----------------------------+

My question is what will be the correct way to insert the data to the table.

Currently what i am doing is:

$date = date("Y-m-d");    

$result = mysql_query("SELECT Count(*) as count FROM table WHERE date = '$date'");
$row = mysql_fetch_array($result);
$count = $row['count'];

if($count > 0){
mysql_query("UPDATE table SET visits = visits+1 WHERE date = '$date'");
}else{
mysql_query("INSERT INTO table (`date`, `visits`) VALUES ('$date', '1');");
}

Is this the right way to update the table or is there a better one? Is it possible to update it only with one sql query, for example like this:

mysql_query("If row exists update table else insert");

I dont know if this is possible.

Thank you very much in advance! id be very grateful if you can help me on this.

Take a look at `INSERT...ON DUPLICATE...` in the MySQL manual [here](https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html) — , Mar 24 '14 at 05:08
Possible duplicate: http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql — Aziz Shaikh, Mar 24 '14 at 05:08

Tun Zarni Kyaw · Accepted Answer · 2014-03-24T05:16:02.440

3

Yes, if your date is the key in the table

INSERT INTO `table`(`date`, `visits`) 
VALUES(`$date`, `1`)
ON DUPLICATE KEY UPDATE
`visits`=`visits`+1

Reference: MYSQL:: INSERT ... ON DUPLICATE KEY UPDATE

Note: you are using mysql_* functions those are deprecated (means outdated, no longer supported, no longer modified), so try to use mysqli_* or PDO.

edited Mar 24 '14 at 05:16

answered Mar 24 '14 at 05:10

Tun Zarni Kyaw

2,099
2
21
27

Thank you very much! what happens if `date` is not an unique index, it can be duplicated because of a column `country` . Should i create a column `id` to be the key and auto_increment? – Lucas Mar 24 '14 at 05:33
1

no, you don't need to create `id` column, you can set `country` and `date` as composite key, and use all key field in the insert into statement - `INSERT INTO table(country, date, visits)` – Tun Zarni Kyaw Mar 24 '14 at 05:36

score 0 · Answer 2 · answered Mar 24 '14 at 05:09

0

TRY

INSERT INTO `tabelname`(`date`, `visits`) VALUES ($date,1) 
ON DUPLICATE KEY UPDATE `visits` = `visits`+1

note: must set date column as UNIQUE index

tip: do not use mysql keyword as column name (date)

Reference

answered Mar 24 '14 at 05:09

xkeshav

53,360
44
177
245

Thank you very much! what happens if `date` is not an unique index, it can be duplicated because of a column `country` . Should i create a column `id` to be the key and auto_increment? – Lucas Mar 24 '14 at 05:30

Is it possible to make an insert or an update in the same sql query?

2 Answers2