Posts: 0
Threads: 0
Joined: Mar 2022
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
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.
For example:
```sql
INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19);
```
Let’s say the unique key is `ID`, and in my **Database**, there is a row with `ID = 1`. In that case, I want to update that row with these values. Normally this gives an error.
If I use `INSERT IGNORE` it will ignore the error, but it still won’t update.
|
Posts: 0
Threads: 0
Joined: Sep 2016
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Try this out:
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
|
Posts: 0
Threads: 0
Joined: Jun 2018
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Try this:
`INSERT INTO table (id,name,age) VALUES('1','Mohammad','21') ON DUPLICATE KEY UPDATE name='Mohammad',age='21'`
Note:
Here if id is the primary key then after first insertion with `id='1'` every time attempt to insert `id='1'` will update name and age and previous name age will change.
|
Posts: 0
Threads: 0
Joined: Oct 2017
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
When using SQLite:
REPLACE into table (id, name, age) values(1, "A", 19)
Provided that `id` is the primary key. Or else it just inserts another row. See *[INSERT][1]* (SQLite).
[1]: [To see links please register here]
|
Posts: 0
Threads: 0
Joined: Oct 2020
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
When using batch insert use the following syntax:
INSERT INTO TABLE (id, name, age) VALUES (1, "A", 19), (2, "B", 17), (3, "C", 22)
ON DUPLICATE KEY UPDATE
name = VALUES (name),
...
|
Posts: 0
Threads: 0
Joined: Jan 2023
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Use [`INSERT ... ON DUPLICATE KEY UPDATE`][1]
**QUERY:**
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name="A", age=19
[1]: [To see links please register here]
|
Posts: 0
Threads: 0
Joined: Sep 2017
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Just because I was here looking for this solution but for updating from another identically-structured table (in my case website test DB to live DB):
INSERT live-db.table1
SELECT *
FROM test-db.table1 t
ON DUPLICATE KEY UPDATE
ColToUpdate1 = t.ColToUpdate1,
ColToUpdate2 = t.ColToUpdate2,
...
As mentioned elsewhere, only the columns you want to update need to be included after `ON DUPLICATE KEY UPDATE`.
No need to list the columns in the `INSERT` or `SELECT`, though I agree it's probably better practice.
|
Posts: 0
Threads: 0
Joined: Mar 2023
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
In my case i created below queries but in the first query if `id` 1 is already exists and age is already there, after that if you create first query without `age` than the value of `age` will be none
REPLACE into table SET `id` = 1, `name` = 'A', `age` = 19
for avoiding above issue create query like below
INSERT INTO table SET `id` = '1', `name` = 'A', `age` = 19 ON DUPLICATE KEY UPDATE `id` = "1", `name` = "A",`age` = 19
may it will help you ...
|
Posts: 0
Threads: 0
Joined: Sep 2016
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
In case, you want to keep old field (For ex: name). The query will be:
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name=name, age=19;
|
Posts: 0
Threads: 0
Joined: May 2021
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
In case that you wanted to make a `non-primary` fields as criteria/condition for `ON DUPLICATE`, you can make a `UNIQUE INDEX` key on that table to trigger the `DUPLICATE`.
ALTER TABLE `table` ADD UNIQUE `unique_index`(`name`);
And in case you want to combine two fields to make it unique on the table, you can achieve this by adding more on the last parameter.
ALTER TABLE `table` ADD UNIQUE `unique_index`(`name`, `age`);
Note, just make sure to delete first all the data that has the same `name` and `age` value across the other rows.
DELETE table FROM table AS a, table AS b WHERE a.id < b.id
AND a.name <=> b.name AND a.age <=> b.age;
After that, it should trigger the `ON DUPLICATE` event.
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name = VALUES(name), age = VALUES(age)
|
|