Home » SQL & PL/SQL » SQL & PL/SQL » SQL Update statement for OLTP table (ORACLE 11G)
SQL Update statement for OLTP table [message #584421] Tue, 14 May 2013 04:35 Go to next message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

Hi All,

I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers.
I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.

The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way.
My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column


Please find below the sample code for initial data setup. Thanks in advance.

--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------


CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
					  INVENTORY_CNT NUMBER ( 2 ) );

CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
					 ITEMSEQ NUMBER ( 5 ) );

INSERT INTO
	  TEMP_TEST_1
VALUES
	  ( 1,
	    4 );

INSERT INTO
	  TEMP_TEST_1
VALUES
	  ( 2,
	    0 );

INSERT INTO
	  TEMP_TEST_1
VALUES
	  ( 3,
	    10 );

COMMIT;        

--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------

INSERT INTO
	  TEMP_INV_1
VALUES
	  ( 1,
	    11 );

INSERT INTO
	  TEMP_INV_1
VALUES
	  ( 1,
	    12 );

INSERT INTO
	  TEMP_INV_1
VALUES
	  ( 1,
	    13 );

INSERT INTO
	  TEMP_INV_1
VALUES
	  ( 1,
	    14 );

COMMIT;        
        

[Updated on: Tue, 14 May 2013 04:43]

Report message to a moderator

Re: SQL Update statement for OLTP table [message #584429 is a reply to message #584421] Tue, 14 May 2013 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not store computed columns.
See Trigger-summing values, it's along topic but it demonstrate why the total is almost always wrong with all ways you can imagine to update it.

Regards
Michel
Re: SQL Update statement for OLTP table [message #584432 is a reply to message #584429] Tue, 14 May 2013 05:48 Go to previous messageGo to next message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

HI Michel,

Thanks for your quick response. Though it is a bad practice to store computed columns, this is designed more than a decade and I need to live with this.

So I am looking for a safer way to update this column which is also critical for OLTP since this is a precursor for the store orders.

Considering this design, is it better to go for a X=X+(Count of new rows) OR X= Count of all available rows?

Cheers
Re: SQL Update statement for OLTP table [message #584447 is a reply to message #584432] Tue, 14 May 2013 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So I am looking for a safer way


There is NO safer way. All ways are wrong; change the table to a view this is the only correct way.
When I say all ways are wrong, I meant all ways leads to wrong results.
Read the link I posted.

Regards
Michel
Re: SQL Update statement for OLTP table [message #584460 is a reply to message #584447] Tue, 14 May 2013 09:02 Go to previous message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

I did read the link and thanks a lot for the explanations. Cheers.
Previous Topic: convert time part into second
Next Topic: How to find table name and column name
Goto Forum:
  


Current Time: Fri Apr 18 11:56:24 CDT 2014

Total time taken to generate the page: 0.10922 seconds