October 21, 2009 at 12:36 pm
I am a new bee to SQL Server. Please help me.
I have a table like
OrderNo OrderDate Cost PreCost NewCost
1 10/20/2009 100 10 0
2 10/19/2009 105 0 105
1 09/20/2009 10
If the same orderno exists in previous month, then update the precost column with previous month cost other wise update the newcost column
Thanks.
007
October 22, 2009 at 6:44 am
In SQL Server you can do something like this:
UPDATE Orders
SET precost = O2.cost
FROM
orders O JOIN
orders O2 ON
O.OrderNo = O2.OrderNo AND
/*
this limits it to previous month
*/
O2.OrderDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, O.OrderDate), 0) AND
O2.OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, O.OrderDate) - 1, 0)
This has not been tested, but it should get you started. You could also use a cte to do the update.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2009 at 12:55 pm
Jack,
Getting following error
Msg 8154, Level 16, State 1, Line 1
The table 'Orders' is ambiguous.
here is the script
CREATE TABLE ORDERS(OrderNo int, OrderDate datetime, Cost int, PreviousCost int, NewCost int)
Delete from Orders
INSERT INTO ORDERS VALUES(1, '2009-09-19', 100,0,0)
INSERT INTO ORDERS VALUES(2, '2009-10-19', 200,0,0)
INSERT INTO ORDERS VALUES(1, '2009-10-19', 10,0,0)
Select * from ORDERS
007
October 22, 2009 at 1:08 pm
In the update statement, you need to use the Alias that was created by Jack for the table name,
Where it's written
Update Orders
From Orders O
join Orders O2....
It should read
Update O
From Orders O
join Orders O2....
Because you aliased the table, well, Jack did, then you need to use that name for the query.
Cheers,
J-F
October 22, 2009 at 1:09 pm
Change Update ORders to update O
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2009 at 1:33 pm
Jack Corbett (10/22/2009)
Change Update ORders to update O
As you have noticed, Jack used
...FROM
orders O JOIN
orders O2 ON
...
The O and O2 are called table aliasses in the query.
This is a best practise.
Read more about it in books online (BOL)
http://msdn.microsoft.com/en-us/library/ms187455%28SQL.90%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply