I have the following upsert with which I have problems because the subqueries give me more than one result. The problem is that I don’t know how to indicate in the upsert to compare the value of the column that is currently being updated. The problem is after the DO UPDATE. Answer I think you are lookin…
Tag: upsert
Enforce 2 unique constraints on upserts in Postgres
I have a table where I save contacts data I add data to this table from several sources. Some sources have Linkedin profiles data about people, other sources have email data. Sometimes the full names are not equal, even if they refer to the same person. And I want to do upserts to not have duplicated data. Fo…
Upsert (merge) for updating record if it exists and inserting otherwise
I am trying to write a DB2 query that allows me to either update a record if it already exists but if it does not exist it should be inserted. I wrote the following query that should accomplish this: This query should attempt to check if a record already exists for the selection criteria. Updating a record se…
How do I insert multiple records with an ON CONFLICT UPDATE clause in Postgres?
Given this table, which is just an example. I need to do an upsert on multiple records. payment_status returns as ambiguous. The problem I’m trying to solve here is that I have a process that imports data but if it encounters a conflict, it needs to update the current data with the new data. (it’s…
PostgreSQL UPSERT (INSERT … ON CONFLICT UPDATE) fails
I have a row in my postgresql database that I want to update. My code generates this SQL statement to insert else update the one field that’s changed: What I don’t understand is that I can select that one row and it is present with a non-NULL num_syncs. But the UPSERT is failing because it doesn&#…
Column name is ambiguous on UPSERT action
I am trying to run an UPSERT query string on a postgres db – insert into business_data_test (select * from business_data_tmp) on conflict(phone_number) do update set average_expense=(…
Use extra columns in INSERT values list in ON CONFLICT UPDATE
I have an UPSERT query where I want to insert value y value in column b, but if it already exists I want to update it with value z. How can I achieve this? P.S: A simple value list (without select) did not work because we cannot have more columns in values list than we are inserting. Answer In the
Where clause in upsert conflict target
Suppose I have a table created like this: When attempting to insert a new row, there are three possibilties: No row for ‘name’ exists. The new row should be inserted. A row for ‘name’ exists that has a value of NULL. The existing row should be updated with the new value. A row for R…
VBA SQL update/insert to local table using values from form
I am trying to update a row in a local table stored in access (or insert a new row if that ID doesn’t already exist) using values populated in a form by the user. I am writing it in VBA using SQL. …
Race Condition between SELECT and INSERT for multiple columns
Note: This is a question which is a follow up of this solution. You need to read the link to get context for this question. Also, this is for postgres v9.4 If we want to return multiple columns now …