473,230 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

insert/update

I seemed to remember being able to do this but I can't find the docs.

Can I run a sql query to insert new or update existing rows in one query?

Otherwise I have to run a select query to see if it's there and then
another one to update/insert.

What I'm trying to do is create a counter for each key, insert a value
of 1 or increment the value by 1 and then set another specific row
(where key = $key) to always increment by 1.

And the more I type, the more this sounds like the answer is going to be
part function, part trigger.... Maybe I should post to 'novice' for a
while! ;)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
6 2573

On 26/05/2004 11:54 Tom Allison wrote:
I seemed to remember being able to do this but I can't find the docs.

Can I run a sql query to insert new or update existing rows in one query?

Otherwise I have to run a select query to see if it's there and then
another one to update/insert.

What I'm trying to do is create a counter for each key, insert a value
of 1 or increment the value by 1 and then set another specific row
(where key = $key) to always increment by 1.

And the more I type, the more this sounds like the answer is going to be
part function, part trigger.... Maybe I should post to 'novice' for a
while! ;)


Use a sequence.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
Paul Thomas wrote:
On 26/05/2004 11:54 Tom Allison wrote:
What I'm trying to do is create a counter for each key, insert a value
of 1 or increment the value by 1 and then set another specific row
(where key = $key) to always increment by 1.

Use a sequence.


Not sure it's going to help him here. Looks like a specific count is needed.

Tom - you don't say precisely what you're trying to do, but I like to
keep my code simple by making sure there is always a row available.

Example (a poor one, perhaps):
cart_details (cart_id, owner, ...)
cart_summary (cart_id, num_items, tot_value)
cart_items (cart_id, item_id, quantity)

Create a trigger on cart_details that after inserting a new row, inserts
zeroed totals into cart_summary. That way when you add new items to the
cart, you know there is always a total to update.

On the other hand, you might need cart_summary to be something like:
cart_summary (cart_id, item_category, num_items, tot_value)
In this case you either create zeroed totals for every value of
"item_category" or you need a trigger on cart_items rather than
cart_details. If the trigger is on cart_items and you can have more than
one user adding items to the cart at the same time, then you'll need to
think about concurrency issues and locking.

Useful sections of the manual are "Procedural Languages:pl/pgsql" and
"SQL command reference". You can probably find example triggers via the
techdocs site.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3
--- Tom Allison <ta******@tacocat.net> wrote:
I seemed to remember being able to do this but I
can't find the docs.

Can I run a sql query to insert new or update
existing rows in one query?

Otherwise I have to run a select query to see if
it's there and then
another one to update/insert.
This is what you have to do.

This question comes up a lot on the lists. You can
read endless discussions about it if you want to
search the archives.

The issue is concurrency, i.e. multiple users
accessing the data at the same time, and perhaps two
of them wanting to do the same update-else-insert
combination at the same time. Then you have the so
called "race condition", i.e. user1 does a select,
finds the record does not exist, attempts to insert;
in between those, user2 inserts the row. So, you now
either have duplicate data (bad), or user1's insert
fails because of a unique constraint (also bad,
because the operation has failed).

The only way to guarantee against this is to lock the
table for the duration of the exercise, which prevents
any concurrent access at all. This may be acceptable
if you have few users, or a low insert/update load,
but may be a performance killer otherwise.

Every now and then someone pops up on the list(s)
claiming to have found some new miracle method for
getting around these limitations, but no such has yet
been proven.

What I'm trying to do is create a counter for each
key, insert a value
of 1 or increment the value by 1 and then set
another specific row
(where key = $key) to always increment by 1.

And the more I type, the more this sounds like the
answer is going to be
part function, part trigger.... Maybe I should post
to 'novice' for a
while! ;)
---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please
send an appropriate
subscribe-nomail command to
ma*******@postgresql.org so that your
message can get through to the mailing list

cleanly

__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

Richard Huxton <de*@archonet.com> writes:
Tom - you don't say precisely what you're trying to do, but I like to keep my
code simple by making sure there is always a row available.


Or alternatively you could always try to insert the record with a count of 0
then increment. If the insert fails due to a duplicate key violation you could
just ignore the error.

That suffers from doing twice as many queries as necessary all the time. You
could try doing the update then check the result to see how many records were
updated, if 0 then try doing the insert ignoring any errors and then repeat
the update.

But then your code is getting kind of complex... And both of these assume
nobody's deleting records.

The more usual solution is to always try either the update or the insert, and
in the case of a duplicate key violation or 0 updated rows, then try the
other. To do this properly you have to do it in a loop, since some other
process could be inserting or deleting between the two queries.

FWIW the feature you're looking for is indeed a new feature in the latest SQL
standard and there's been some talk of how to implement it in a future version
of Postgres. I would expect to see it come along sometime, though probably not
in 7.5.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #5
Jeff Eckermann wrote:
--- Tom Allison <ta******@tacocat.net> wrote:
I seemed to remember being able to do this but I
can't find the docs.

Can I run a sql query to insert new or update
existing rows in one query?

Otherwise I have to run a select query to see if
it's there and then
another one to update/insert.

This is what you have to do.

This question comes up a lot on the lists. You can
read endless discussions about it if you want to
search the archives.

The issue is concurrency, i.e. multiple users
accessing the data at the same time, and perhaps two
of them wanting to do the same update-else-insert
combination at the same time. Then you have the so
called "race condition", i.e. user1 does a select,
finds the record does not exist, attempts to insert;
in between those, user2 inserts the row. So, you now
either have duplicate data (bad), or user1's insert
fails because of a unique constraint (also bad,
because the operation has failed).

The only way to guarantee against this is to lock the
table for the duration of the exercise, which prevents
any concurrent access at all. This may be acceptable
if you have few users, or a low insert/update load,
but may be a performance killer otherwise.


So I have to watch out for transactions on this?
Essentially what I'm trying to do is one of the following two:

if exists update a field to field+1 on one record
if it doesn't exist, insert a row with field = 1
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #6
--- Tom Allison <ta******@tacocat.net> wrote:
Jeff Eckermann wrote:
--- Tom Allison <ta******@tacocat.net> wrote:
I seemed to remember being able to do this but I
can't find the docs.

Can I run a sql query to insert new or update
existing rows in one query?


So I have to watch out for transactions on this?
Essentially what I'm trying to do is one of the
following two:

if exists update a field to field+1 on one record
if it doesn't exist, insert a row with field = 1


I'm not sure what you are asking here that is not
already covered. I suggest you spend some time
reading the documentation on concurrency, and
searching the archives for some of the lengthy past
discussions on this topic.


__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
14
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
20
by: Mark Harrison | last post by:
So I have some data that I want to put into a table. If the row already exists (as defined by the primary key), I would like to update the row. Otherwise, I would like to insert the row. I've...
3
by: V T | last post by:
Hello all, SQL Server 2000 documentation http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx states that if view is using "NOT NULL" columns of a base table, then...
1
by: abhi81 | last post by:
Hello All, I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp....
4
by: =?Utf-8?B?UmljaA==?= | last post by:
On a form - I have a datagridview which is docked to the entire form. The datagridview allows users to Delete and/or Add Rows. On the Form_Load event I Fill the datagridview source table with a...
0
by: magnolia | last post by:
i created a trigger that will record the changes made to a table .everything works fine except the insert query.whenerever i try to insert a record it fires insert and update triger at the same time...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.