
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Add constraint for on duplicate key update in MySQL
To add constraint, the syntax is as follows −
alter table yourTableName add constraint anyName unique(yourColumnName);
Let us first create a table −
mysql> create table DemoTable1459 -> ( -> Name varchar(20), -> Score int -> ); Query OK, 0 rows affected (0.72 sec)
Following is the query to add constraint for on duplicate key update −
mysql> alter table DemoTable1459 add constraint Name_const unique(Name); Query OK, 0 rows affected (1.09 sec) Records: 0 Duplicates: 0 Warnings: 0
Insert some records in the table using insert command −
mysql> insert into DemoTable1459 values('Chris',57) on duplicate key update Score=Score+10; Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1459 values('David',89) on duplicate key update Score=Score+10; Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1459 values('Chris',78) on duplicate key update Score=Score+10; Query OK, 2 rows affected (0.33 sec)
Display all records from the table using select statement −
mysql> select * from DemoTable1459;
This will produce the following output −
+-------+-------+ | Name | Score | +-------+-------+ | Chris | 67 | | David | 89 | +-------+-------+ 2 rows in set (0.00 sec)
- Related Articles
- Implement INSERT … ON DUPLICATE KEY UPDATE in MySQL
- Implementing INSERT… ON DUPLICATE KEY UPDATE in MySQL
- MySQL error 1452 - Cannot add or update a child row: a foreign key constraint fails?
- Correct MySQL INSERT ... ON DUPLICATE KEY syntax?
- MySQL error 1452 - Cannot add or a child row: a foreign key constraint fails
- Update table with duplicate ids in MySQL
- Finding Duplicate Rows in MySQL (Composite Key)?
- How to update DB2 table with a duplicate primary key?
- How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
- How can we assign FOREIGN KEY constraint on multiple columns?
- What is the difference between MySQL PRIMARY KEY and UNIQUE constraint?
- How can we remove composite PRIMARY KEY constraint applied on multiple columns of an existing MySQL table?
- How to add not null constraint to existing column in MySQL?
- Add a positive integer constraint to an integer column in MySQL?
- Search and update array based on key JavaScript

Advertisements