Home » SQL & PL/SQL » SQL & PL/SQL » Correct SQL?
Correct SQL? [message #225118] Sat, 17 March 2007 20:22 Go to next message
jman27
Messages: 37
Registered: September 2005
Member
Is this SQL correct? It appears to run correctly, however, I don't want to make sure it always sets the correct results:

UPDATE itemgroup SET totalitems=58, itemprice=15.99, itemtotalamt=totalitems*itemprice WHERE itemid=103


Basically, the query is called by a web form that sets the totalitems and the itemprice. The itemtotalamt should compute the number of items x the price of each item. I wasn't sure if a statement like this was legal and also wasn't sure if the itemtotalamt=totalitems*itemprice would use the old values since the new values (58 and 15.99) hadn't technically been set yet.

I could write it as:

UPDATE itemgroup SET totalitems=58, itemprice=15.99, itemtotalamt=58*15.99 WHERE itemid=103


however, I've simplified the query for this post. The itemprice is actually found by calling a function. The function does quite a few additional things to compute the price... I was trying to avoid calling the function twice in the UPDATE query above.

If any of this is confusing, let me know and I'll rephrase. I basically just wanted to know if the very first query above was valid and if the itemtotalamt=totalitems*itemprice would ALWAYS use the new values also being set in the same UPDATE query (hopefully that made sense).

Thanks.

[Updated on: Sat, 17 March 2007 20:25]

Report message to a moderator

Re: Correct SQL? [message #225119 is a reply to message #225118] Sat, 17 March 2007 20:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
1) I'm not sure why this is an "expert" question.
2) What prevents YOU from running the SQL yourself & see what happens. Remember you can always ROLLBACK if you don't like the results.

If you'll blindly accept the word of total stangers at face value, then I have bridge I'll sell you really cheap. Interested?
Re: Correct SQL? [message #225120 is a reply to message #225119] Sat, 17 March 2007 20:39 Go to previous messageGo to next message
jman27
Messages: 37
Registered: September 2005
Member
In my question I stated that it did give me correct result, however, I was interested in reading whether or not this was a safe query to execute 100% of the time to expect the results I described. The query runs more efficiently (faster) when I do it the way I described, however, it looked fishy.

Yes, I ran it, and yes, it worked as expected. But did I overlook something? Is it common to run queries that update other columns by multiplying 2 other columns together? In the same query?

It's a simple question that really didn't need sarcastic answers. Please don't take offense by that... I've always received great feedback on these forums and didn't expect an answer like that.
Re: Correct SQL? [message #225121 is a reply to message #225118] Sat, 17 March 2007 20:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> Is it common to run queries that update other columns by multiplying 2 other columns together?
NO. It is NOT an accepted practice to store computed values as you are doing because at a later time one of the "input" values could be changed by a later DML statement & the "results" column does NOT necessarliy get recalculated at that time.
Then data inconsistencies can result.

Correctness is in the eye of the beholder & depends upon when being viewed.

[Updated on: Sat, 17 March 2007 20:57] by Moderator

Report message to a moderator

Re: Correct SQL? [message #225207 is a reply to message #225121] Mon, 19 March 2007 02:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you certain that you've run that SQL and seen it work?
I would have expected it to return the wrong result, as the update statement should be using the old values to do the calculation (See example).
Are you sure that there isn't a trigger on the table doing the calculation for you?

Example:
SQL> create table update_Test (col_1 number, col_2  number, col_3  number);
Table created.

insert into update_Test values (1,1,1);
insert into update_Test values (1,2,2);
insert into update_Test values (2,2,4);

SQL> select * from update_test;

     COL_1      COL_2      COL_3
---------- ---------- ----------
         1          1          1
         1          2          2
         2          2          4

SQL> update update_test set col_1 = col_1+1, col_2 = col_2 + 1, col_3 = col_2*col_1;

3 rows updated.

SQL> select * from update_test;

     COL_1      COL_2      COL_3
---------- ---------- ----------
         2          2          1
         2          3          2
         3          3          4
Previous Topic: query needed
Next Topic: Counting rows
Goto Forum:
  


Current Time: Thu Dec 08 04:29:17 CST 2016

Total time taken to generate the page: 0.12614 seconds