UPSERT: SQL-UPDATE a row, or if not found INSERT a new row.

learn more… | top users | synonyms

2
votes
2answers
52 views

Call UPSERT function multiple times to different inputs

I have a function: merge_vehicles(vid, cid, vname, reg_no, name, name_1st) Can i call it multiple times on inputs like (2335, 55, '246BDH', '246BDH', '811', 1), (2336, 55, '038THX', '038THX', ...
2
votes
1answer
212 views

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
1
vote
1answer
56 views

Upsert statement never completes, infinite loop?

Edit: The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a ...
0
votes
0answers
52 views

Auto increment holes in primary key - any issues?

I have an application that is creating large holes in the auto incremented primary surrogate key to the table as it uses lots of INSERT ... ON DUPLICATE KEY UPDATE queries. I'm looking into changing ...
0
votes
0answers
85 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
2
votes
1answer
178 views

How to use proper hints for Upsert involving a top 1 subquery

For this specific case (tracking Load Balancer switches) we wish to optimize an Upsert so that it does not exhibit any race conditions, cause any PK violation, or acquire any over sized locks. ...
1
vote
1answer
113 views

scenario to update or insert big amount of data

I try to find the best scenario to update or insert 1 million contacts in my postgres database. My actual scenario is: read contacts 1000 per 1000 and try to insert multiple rows with one request ...
0
votes
1answer
608 views

MongoDB fast upsert

For non-incremental, but overlapping, imports, I worry about upserts to be very slow and locking the resources for hours. From different sources, I receive 1 json dump per day per source for up to 3 ...
1
vote
1answer
1k views

Pass a table array as a parameter to an upsert function in postgresql

i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
0
votes
2answers
994 views

Interactive INSERT / UPDATE function to implement UPSERT

I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 ...
6
votes
3answers
555 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
5
votes
2answers
6k views

Separate stored procedures for inserts and updates?

I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures: InsertNewPerson UpdatePertsonById But I was thinking to write ...
2
votes
1answer
6k views

UPSERT - Is there a better alternative to MERGE or @@rowcount? [closed]

I was wondering if you have encountered a T-SQL command similar to the concept of UPSERT? Performing INSERT|UPDATE operations using options (1) or (2) seems overly complex and error prone. OBJECTIVE ...
6
votes
1answer
467 views

what is mongodb telling me when it logs an update?

After several minutes of a large program that pushes a lot of data into mongo, my log starts to show a message for (I guess) each update. This seems awfully noisy, is there some special reason it ...
3
votes
3answers
6k views

INSERT INTO without duplicates

I want to do this INSERT INTO AdminAccounts (Name) SELECT Name FROM Matrix But I don't want to create duplicates. ie, I ran this a few weeks ago and need to update the data.
9
votes
3answers
646 views

Need Help Troubleshooting Sql Server 2005 Deadlock Scenario

I am running into a deadlock scenario where the only participants in the deadlock appear to be a single table and a single stored procedure that deletes from that table. I drew that conclusion based ...
1
vote
3answers
3k views

ON DUPLICATE KEY UPDATE question

I'm trying to compare data from two systems. The basic idea is: Create Temp Table. Insert Table1 Data. Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected. Filter where ...