

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 Questions & Answers
- 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 assign FOREIGN KEY constraint on multiple columns?
- How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
- Search and update array based on key JavaScript
- What is the difference between MySQL PRIMARY KEY and UNIQUE constraint?
- What happens if I will add a UNIQUE constraint on the same column for multiple times?
- 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?
Advertisements