pre-existing table: create new field and populate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pre-existing table: create new field and populate

Author

Topic

brianp
Starting Member

3 Posts

Posted - 2008-11-20 : 17:33:03

Hey Everyone,
I need some help, I feel I need a loop function but haven't done much looping in SQL before.

Heres the setup

tblOrders
---------
OrderID
CustomerID
SomeOtherColumns

tblOrderDetails
---------
OrderID
ProductID
UnitPrice
Quantity
Discount

I've added a new column to tblOrders called TotalSales. I'd like to populate all the rows based off the tblOrderDetails data.

The tblOrders table has a few thousand rows so doing it by hand would be tedious.

The half procedure i have for doing it in a single row is (also im using ms sql manager unfortunately it's for a school project, but normally i use mysql):

DECLARE @tblOrdernum INTEGER;
DECLARE @totalPrice MONEY

SET @tblOrdernum = "loopvar"

SET @totalPrice = (SELECT SUM(UnitPrice*Quantity) FROM tblOrderDetails WHERE OrderID = @tblOrdernum)

UPDATE tblOrders
SET TotalSales = @totalPrice
WHERE OrderID = @tblOrdernum

I'm hoping with every loop i can select and use the order id where i have "loopvar".

Does it make sense to do it this way?

Any help would be nice

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-20 : 17:44:47

update tblOrders
set TotalSales = (select sum(UnitPrice*Quantity) FROM tblOrderDetails d WHERE d.OrderID = o.OrderID)
from tblOrders o

update tblOrders
set TotalSales = t.TotalSales
from tblOrders o
join (select OrderID, TotalSales = sum(UnitPrice*Quantity) FROM tblOrderDetails group by OrderID) t
on o.OrderID = t.OrderID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brianp
Starting Member

3 Posts

Posted - 2008-11-20 : 22:56:45

quote:
Originally posted by nr

update tblOrders
set TotalSales = (select sum(UnitPrice*Quantity) FROM tblOrderDetails d WHERE d.OrderID = o.OrderID)
from tblOrders o

update tblOrders
set TotalSales = t.TotalSales
from tblOrders o
join (select OrderID, TotalSales = sum(UnitPrice*Quantity) FROM tblOrderDetails group by OrderID) t
on o.OrderID = t.OrderID

Hey both of these worked awesome, and I 98% understand how/why they work. Now for that 2%.

I've never used single letters defined after a statement before.
From your example it looks like your just setting a tableName or select statement (which i would return a value) to a short variable ?

Now based on that idea, im confused as to how in:
update tblOrders
set TotalSales = (select sum(UnitPrice*Quantity) FROM tblOrderDetails d WHERE d.OrderID = o.OrderID)
from tblOrders o

you used o.OrderID before you actually set o as tableOrders?

Thanks for your help so far,
brianp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 01:45:11

its not before. the subquery is evaluated after FROM so it will understand o as tblOrders as its in same context.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-21 : 03:07:21

It's called a derived table - the letter is an alias for the resultset.
the set statement wotks on the resultset from the query - don't think of it as being i the order in which it is written (although that can help sometimes)

update tblOrders
set TotalSales = (select sum(UnitPrice*Quantity) FROM tblOrderDetails d WHERE d.OrderID = o.OrderID)
from tblOrders o

o is an alias for tblOrders and as the from clause is needed for the select then that alias is available in the correlated subquery. The subquery is correlated as it references a column in the outer query - think of it as being executed for each row.

Good questions.

the query could also be

update o
set TotalSales = (select sum(UnitPrice*Quantity) FROM tblOrderDetails d WHERE d.OrderID = o.OrderID)
from tblOrders o

It only knows that the update is on tblOrders as it is only aliased once.

If you had
update tblOrders
set TotalSales = (select sum(UnitPrice*Quantity) FROM tblOrderDetails d WHERE d.OrderID = o.OrderID)
from tblOrders o
join tblOrders o2
on o.OrderID = o2.o.OrderID

then you would get an error as the optimiser doesn't know which version of tblOrders to update.

In this case I usually do
update o --tblOrders
set TotalSales = (select sum(UnitPrice*Quantity) FROM tblOrderDetails d WHERE d.OrderID = o.OrderID)
from tblOrders o
join tblOrders o2
on o.OrderID = o2.o.OrderID

to aid understandability

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brianp
Starting Member

3 Posts

Posted - 2008-11-22 : 13:43:50

Wow thanks for the awesome explanation!

Now that I see a little more of what's going on here I'm going to see if i can explain outloud what was happening in the 2nd example.

update tblOrders
set TotalSales = t.TotalSales
from tblOrders o
join (select OrderID, TotalSales = sum(UnitPrice*Quantity) FROM tblOrderDetails group by OrderID) t
on o.OrderID = t.OrderID

alias t is a derived table with the results from the selected query.

set TotalSales = t.TotalSales takes the TotalSales from said derived table and sets it in the original tblOrders table.

hmmm I wish i had this concept in my head for all my previous questions.

Now im used to top to bottom execution. So with these is it like using parenthesis in math equations. Anything in parenthesis (the sub queries) are executed first setting up all derived tables ?

or (just had an epiphany) when t.TotalSales is called the table is derived. using t is calling that query everytime it is used?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-22 : 14:13:14

Think oyu're correct

>> Anything in parenthesis (the sub queries) are executed first
You can think of it that way (and should) but the optimiser can do whatever it wishes as long as it gets the functionality right. It's worth looking at the query plen to see what it actully does.

t is the exposed alias for the table. It can't be accessed as table name (except in the update clause itself maybe)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources