Questions tagged [insert-update]
A mixed flavour of data entry mode and data modification mode. It can be used when insert is required for case of non-existing data and modification if data exists.
insert-update
109
questions
1171
votes
13
answers
1.3m
views
Insert into a MySQL table or update if exists
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:
INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19);
Let’s ...
- 14.6k
396
votes
7
answers
393k
views
How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?
A very frequently asked question here is how to do an upsert, which is what MySQL calls INSERT ... ON DUPLICATE UPDATE and the standard supports as part of the MERGE operation.
Given that PostgreSQL ...
- 319k
158
votes
2
answers
66k
views
How do I update if exists, insert if not (AKA "upsert" or "merge") in MySQL?
Is there an easy way to INSERT a row when it does not exist, or to UPDATE if it exists, using one MySQL query?
- 2,166
165
votes
8
answers
443k
views
SQL Server insert if not exists best practice [closed]
I have a Competitions results table which holds team member's names and their ranking on one hand.
On the other hand I need to maintain a table of unique competitors names:
CREATE TABLE Competitors (...
- 3,433
151
votes
4
answers
181k
views
INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE
I'm doing an insert query where most of many columns would need to be updated to the new values if a unique key already existed. It goes something like this:
INSERT INTO lee(exp_id, created_by,
...
- 20.4k
15
votes
3
answers
48k
views
Update the value of a field in database by 1 using codeigniter
I want to implement a SQL statement using codeigniter active record.
UPDATE tags SET usage = usage+1 WHERE tag="java";
How can I implement this using Codeigniter active records?
- 10k
27
votes
2
answers
74k
views
MySQL trigger On Insert/Update events
So I have two tables like this...
ext_words
-------------
| id | word |
-------------
| 1 | this |
-------------
| 2 | that |
-------------
| 3 | this |
-------------
ext_words_count
-------------...
- 5,034
44
votes
5
answers
72k
views
Does DB2 have an "insert or update" statement?
From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed.
My code now does a select and if no result is returned it does an insert. I really don't like ...
- 493
87
votes
5
answers
264k
views
Update a column in MySQL
I have a table table1 with three columns and a bunch of rows:
[key_col|col_a|col_b]
I want to update col_a with a set of values (i.e. leaving col_b unchanged), something like this:
INSERT INTO ...
- 14.4k
77
votes
4
answers
155k
views
SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE"
I've never seen the syntax INSERT OR REPLACE INTO names (id, name) VALUES (1, "John") used in SQL before, and I was wondering why it's better than UPDATE names SET name = "John" WHERE id = 1. Is there ...
- 113k
3
votes
1
answer
2k
views
MySQL "INSERT ... ON DUPLICATE KEY UPDATE" on Java: How to Differ Inserted/Updated/NoChange states
I'm confused about the return value of MySQL's INSERT ... ON DUPLICATE KEY UPDATE statement. When I try it on a MySQL client (mysql terminal, phpmyadmin or MySQL Workbench), the execution results with ...
- 8,573
68
votes
11
answers
38k
views
.NET Dictionary: get existing value or create and add new value
I often find myself creating a Dictionary with a non-trivial value class (e.g. List), and then always writing the same code pattern when filling in data.
For example:
var dict = new Dictionary<...
- 7,013
16
votes
2
answers
13k
views
Rails - User Input for Multiple models on a single form - How
This is basically a nested form question, albeit with only one field that belongs to a parent model. My data entry form collects data for a model - however I also need to collect one other a data ...
- 463
3
votes
2
answers
20k
views
Update record with previous row
I have a situation where I need to update the records with previous row value.
Source:
|MatId | BaseId |Flag|Pkg1| CS1
--------------------------------
|3001 | 3001 | 1 | 20 | 2 |
|3002 | 3001 ...
- 103
113
votes
2
answers
34k
views
Why are 2 rows affected in my `INSERT ... ON DUPLICATE KEY UPDATE`?
I'm doing an INSERT ... ON DUPLICATE KEY UPDATE for a PRIMARY KEY in the following table:
DESCRIBE users_interests;
+------------+---------------------------------+------+-----+---------+-------+
| ...
- 14.9k