473,714 Members | 2,192 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Table column value update using stored procedure

Dear all,

I have a challenge in hand and am not too sure how to accomplish this
using stored procedure. I have a table containing about 3 fields, but I
need to reorder/renumber a field value every time there is an insert or
delete on the table. Below is the table structure:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
11 1 3
19 1 4
20 1 5
21 1 6
25 1 7

If I've done a delete on tableID = 11, the pageID will be reordered
(below)

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6

Likewise, if I've done an insert on the table the stored procedure
should automatically calculate a new pageID for me and insert it into
place:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6
26 1 7
I have been thinking about doing this in a single stored procedure but
I am stuck with having no idea how to handle select and update in a
single SQL statement...

maybe I should do it in multiple SPs? If so, can anyone show me how? Or
should I do this outside the stored procedure in a middleware
environment where I can handle arrays easier? My concern is doing this
in middleware (PHP, ASPX, python or whatever) will hinder on speed
performance. Please advise.

Thanks.

Jul 14 '06 #1
2 10451
You can have more that 1 sql statement in a stores procedure, so a
mixture of selects and updates.

Why would you want to store this counter number at all though, you can
just get the record you want out on the fly without maintaining these
numbers, for example if you wanted "page id 5" :

SELECT * FROM table ORDER BY tableID LIMIT 5,1

Godzilla wrote:
Dear all,

I have a challenge in hand and am not too sure how to accomplish this
using stored procedure. I have a table containing about 3 fields, but I
need to reorder/renumber a field value every time there is an insert or
delete on the table. Below is the table structure:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
11 1 3
19 1 4
20 1 5
21 1 6
25 1 7

If I've done a delete on tableID = 11, the pageID will be reordered
(below)

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6

Likewise, if I've done an insert on the table the stored procedure
should automatically calculate a new pageID for me and insert it into
place:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6
26 1 7
I have been thinking about doing this in a single stored procedure but
I am stuck with having no idea how to handle select and update in a
single SQL statement...

maybe I should do it in multiple SPs? If so, can anyone show me how? Or
should I do this outside the stored procedure in a middleware
environment where I can handle arrays easier? My concern is doing this
in middleware (PHP, ASPX, python or whatever) will hinder on speed
performance. Please advise.

Thanks.
Jul 17 '06 #2
Hi Paul,

Very true... I found my suggestion above a potential nightmare for tech
to maintain due to the fact that the ID will automatically updated
without their knowledge... Hence I think I will go with your suggestion
indeed. Thank you for your help and have a good day!

Godzilla
pa**********@ho tmail.com wrote:
You can have more that 1 sql statement in a stores procedure, so a
mixture of selects and updates.

Why would you want to store this counter number at all though, you can
just get the record you want out on the fly without maintaining these
numbers, for example if you wanted "page id 5" :

SELECT * FROM table ORDER BY tableID LIMIT 5,1

Godzilla wrote:
Dear all,

I have a challenge in hand and am not too sure how to accomplish this
using stored procedure. I have a table containing about 3 fields, but I
need to reorder/renumber a field value every time there is an insert or
delete on the table. Below is the table structure:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
11 1 3
19 1 4
20 1 5
21 1 6
25 1 7

If I've done a delete on tableID = 11, the pageID will be reordered
(below)

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6

Likewise, if I've done an insert on the table the stored procedure
should automatically calculate a new pageID for me and insert it into
place:

tableID customerID pageID
===== ======== ======
0 1 0
1 1 1
5 1 2
19 1 3
20 1 4
21 1 5
25 1 6
26 1 7
I have been thinking about doing this in a single stored procedure but
I am stuck with having no idea how to handle select and update in a
single SQL statement...

maybe I should do it in multiple SPs? If so, can anyone show me how? Or
should I do this outside the stored procedure in a middleware
environment where I can handle arrays easier? My concern is doing this
in middleware (PHP, ASPX, python or whatever) will hinder on speed
performance. Please advise.

Thanks.
Jul 18 '06 #3

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

Similar topics

0
2618
by: Vladimir Kanovnik | last post by:
I have table with columns id(number), photo(blob) and thumbnail(blob). I would like to insert image (using stored procedure) from file to column photo and in same time copy reduced image to column thumbnail. My code is: CREATE OR REPLACE PROCEDURE "MDEMO"."PUT_PHOTO_THUMB" ( image_file_directory in varchar2, image_file_name in varchar2, image_file_mime_type in varchar2, image_http_path in varchar2,
6
6294
by: dharmadam | last post by:
Is it possible to pass a column name or the order of the column name in the DB2 table table function. For example, I want to update the address of a person by passing one of the address column name like ZIP CODE or ADDRESS LINE. I will call the function with three parameter--UpdateAddress(5,zip_code,person_id) where 5 indicates ZIP_CODE is the fifth column in the table. If 4 is passed, it indicates the address line is to be updated. ...
3
2287
by: dbuchanan | last post by:
Hello, (Windows forms - SQL Server) I fill my datagrid with a stored procedure that includes relationships to lookup tables so that users can see the values of the combobox selections rather than the key value that are stored in the table. It works well if the comboboxes are selected when the row is created.
4
9613
by: fmatamoros | last post by:
I sometimes get the following error from an update statement in a stored procedure: Transaction (Process ID 62) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. The isolation level is READ UNCOMMITTED and there are no explicit transactions in the stored procedure. The update statement is as follows:
1
1611
by: lemes_m | last post by:
Hi All! I have very big problem and I can't continue my Work on my project until i solve it. Here is the situation: TblCalculations FldCalcId FldArticle FldQuantity FldPrice FldAvgPrice 1 1 10.000 0,20 0,20
7
6989
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get into the GUI because it is hard to describe in text. First of all what is the purpose of ALTOBJ()? This procedure was created mostly for ISVs who need to do produce change scripts to upgrade application from release to release, but it can also
1
6475
by: Sharon | last post by:
Hello All, Is it possible to update Sql Table through DataGrid. I have a DataGrid which is being populated through a stored procedure, all i wanted to do is to update one field (FieldName-Authorised) which has a datatype bit through DataGrid but not sure how to go about it. Any Ideas Guys on this one, your help is greatly appreciated. This is the procedure used to populate the datagrid, The primary key for the table is EntryID which...
1
3341
by: jobs | last post by:
I have a growning list of stored procedures that accept a single string as a parameter. The list and parameters are maintained in a table with two columns. Some of the Stored procedures take hours to complete. procedures and the page come right back and recording infomation about the stored procedure (like a job number) that can later be used to report what stored procedures are still running.
1
6434
by: quintonmartin | last post by:
Here's my problem. I have a client application that uses a web service for data access. I can read/update many records in many tables, however, I am not getting what I expect for a varbinary(max) field I am trying to update. Here's how I'm doing it. I have a strongly typed dataset in c#. The column in question is defined as System.Byte LogoImage,
0
9318
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9187
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9080
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9033
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6638
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5961
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4730
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2529
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2113
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.