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.
| Author |
Topic |
|
missMac
Posting Yak Master
124 Posts |
 Posted - 2008-10-15 : 06:28:46
|
Hello,I have a loop that loops through a small set of records. however our db is getting quite large and am not sure how to loop through multiple sets of records How can I change this loop to a view ?update top (1) products set payment = @status, pendingstatus = 'completed', Report = 'Delivered' where batch = @id and Receipient = @final_recipient and Sent_Route = 'Insufficent Credits' I tried using inner join to the productbatch table, but it failsthanks |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-15 : 07:08:49
|
| What are you trying to do here?It looks like you are issuing an UPDATE on TOP 1 of products with no ORDER BY. But then in the WHERE clause you pass the UPDATE an @Id and some parameters. Maybe if you posted some sample data and a description of what you were trying to achieve we could suggest a better way.-------------Charlie |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-15 : 07:23:51
|
A view can only SELECT records, not update.You can update records through a view though.I think what you need is a stored procedure which can accept user supplied parameterscreate procedure dbo.usp_MyUpdate( @id int, @final_recipient varchar(30))ASset nocount onupdate top (1) productsset payment = @status, pendingstatus = 'completed', Report = 'Delivered'where batch = @id and Receipient = @final_recipient and Sent_Route = 'Insufficent Credits'
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-15 : 07:38:35
|
| What's the point of the TOP 1 in the UPDATE if there is no ORDER BY?NB: This is a stupid question!-------------Charlie |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-15 : 07:43:47
|
| thanks guys.No Peso, couldnt get passed it. Basically I put the waitfor clause, ( Whcih does not exist in the real trigger ) because, the calling SP Takes a long time to execute. So as a test, i tried using a wait for clause to see if it was the lenght of execution time.But apparently, it seems it was the trigger waits for the sp to finish executing and based on a timeout, if no response is gotten back it rolls back the transaction |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-15 : 07:55:40
|
Does the UPDATE TOP 1 update 1 random row from a set you've specified in the WHERE clause?That's what I take from books on-line anyway.quote:
You can use the TOP clause to limit the number of rows that are modified in an UPDATE statement. When a TOP (n) clause is used with UPDATE, the update operation will be performed on a random selection of 'n' number of rows. For example, suppose you want to relieve the sales burden of one of your senior sales persons by assigning some customers to a junior sales person. The following query assigns a random sample of 10 customers from one salesperson to another.
Why would you want to do this in this scenario? It looks like you are updating specific reports so I imagine that you would want to either do all that satisfy the where clause or the where clause isolates 1 specific report anyway.-------------Charlie |
|
|
|
|
|
|
|
|
How do i change this to a view ?