Menu
I am building a rating system, and i want to insert a new row, if the name field does not already contain the name i want to insert, and if it does exist, i want to increase the count field by 1
For example, if i have a row the the name 'Tom' and i try to insert another row with the name 'Tom, then i want to +1 for the field count on the row that already exists.
If a row with the name 'Tom' does not exist, i want to insert a new one and set count to 1.
I know i could do this with about 3 SQL statements and some if statements, but that would slow down the script as 2/3 sql commands are being executed.
Any ideas? Thanks!
542
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
No, it is not possible in SQL Server to insert records that have duplicate values in the Primary Key.
If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY , an UPDATE of the old row occurs.
Since both primary key and unique columns do not accept duplicate values, they can be used for uniquely identifying a record in the table. This means that, for each value in the primary or unique key column, only one record will be returned.
see INSERT ... ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.
e.g.
INSERT INTO table (name,counter) VALUES ('Bob', 1)
ON DUPLICATE KEY UPDATE counter=counter+1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
