Insert into a MySQL Table or Update If Exists

Insert into a MySQL table or update if exists

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

MySQL: INSERT or UPDATE if exists, but not based on key column

The most correct way would be to create a unique index on username - course columns and use on duplicate key update.

Obviously, you can issue a select before the insert checking for existing record with same user name and course and issue an insert or an update as appropriate.

UPDATE if exists else INSERT in SQL

The below query will fulfill your requirement.

INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,
`Lunch`, `Bonus`, `Allowance`) values (10000001, 2014, 4, 10.00, 10.00,
10.45, 10.10, 40.55) ON DUPLICATE KEY UPDATE `EmployeeID` = 10000001

mysql insert into table if exists

IF (SELECT count(*)FROM information_schema.tables WHERE table_schema ='databasename'AND table_name ='tablename') > 0
THEN
INSERT statement 
END IF

Use information schema to check existence if table

IF Exists Then Update MySQL

https://stackoverflow.com/a/10095812/1287480 <- Credit where credit is due

INSERT INTO models (col1, col2, col3)
VALUES ('foo', 'bar', 'alpha')
ON DUPLICATE KEY UPDATE col3='alpha';

Insert into SQL table or update record if exists

Edit after your last question edit:

you have in your table this:

`id` int(11) NOT NULL AUTO_INCREMENT,
....
 PRIMARY KEY (`id`)

Your id (PRIMARY KEY) is autoincremental and your question is:

I want to add a row to a database table, but if a row exists with the
same unique key, I want to update the row.

With your querys your never will have the same id because you never set the id value in your query, your query is:

REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`) VALUES ('602633','95','1','2')

and if you don't set the id value in your query the database system autoincrement automatically the id column value, and you never will have to update a row.

---

This should be your query:

$query = "REPLACE INTO audit_section_markrecord(`audit_section_id`,`form_details_subquestion_id`,`form_details_section_id`,`mark`)
    VALUES ('$audit_no','$form_details_subquestion_id','$form_details_section_id','$mark')";

But the audit_section_id, form_details_subquestion_id and form_details_section_id column values must match those of an existing row for the row to be replaced; otherwise, a row is inserted.

You can read more at:

http://dev.mysql.com/doc/refman/5.7/en/replace.html

EXAMPLE:

Consider the table created by the following CREATE TABLE statement:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

When you create this table and run the statements shown in the mysql client, the result is as follows:

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test; 
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

Now if you create a second table almost identical to the first, except that the primary key now covers 2 columns, as shown here (PRIMARY KEY (id, ts)):

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

When you run on test2 the same two REPLACE statements as we did on the original(first) test table, we obtain a different result:

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

This is due to the fact that, when run on test2, both the id and ts column values must match those of an existing row for the row to be replaced; otherwise, a row is inserted.

---

Related Topics