MySQL update content if exists

Question

Ok, so I know there are some questions similar, but how would I update a table like this in MySQL:

+----+----------+---------+-------------------------+
| id | username | subject | content                 |
+----+----------+---------+-------------------------+
| 1  | Fred     | English | Lorem Ipsum             |
+----+----------+---------+-------------------------+

So lets say, i want to update the content in the users English book, however, I want to insert it if it is not already in there, and then in te future update it.

Can one user have more than one English book? If yes, which one do you want to update on insert? — Quassnoi, Apr 12 '16 at 16:00
Possible duplicate of http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists — Sevle, Apr 12 '16 at 16:01

score 0 · Answer 1 · answered Apr 12 '16 at 16:01

If one user can only have one book on a subject, you should create a unique index on (username, subject) and use this query:

INSERT
INTO    mytable (username, subject, content)
VALUES  (:username, :subject, :content)
ON DUPLICATE KEY
UPDATE  content = VALUES(content)

score 0 · Answer 2 · answered Apr 12 '16 at 16:04

First of all you have to create an unique index on the columns you want to be unique. Then the query is like that:

INSERT INTO `tableName` (`username`, `subject`, `content`)
VALUES ('[value 1]', '[value 2]', '[value 3]')
ON DUPLICATE KEY UPDATE `username` = '[value 1]', `subject` = '[value 2]', `content` = '[value 3]';

MySQL update content if exists

2 Answers2