Updating data from table in SP 1

[Mute101]

I have recently moved from access client/server to access client / sql 2000 server and I am looking into ways i can speed up my code.

Scenario:

I have one table of Orders, each row contains an OrderNo field and a MainOrderNo field. When shipped the orders can be combined, at this time the MainOrderNo is set to contain the OrderNo with the despatch details against it.

I need to create a weekly report showing the despatch details against EVERY order that was combined.

Currently I have a Stored Proc creating a table full of the current weeks orders, I need a method of changing the despatch details of every order that has the MainOrderNo field populated.

All the data I need is in the table I have created but how do i update the dataset in the stored procedure?

I know I can use the access client to create recordsets and loop though changing the fields but I am looking for a way to do all the processing server side.

Any help greatly appreciated.

Simon

 

[bperry]

It sounds like what you want is a typical 'set-based' SQl operation that will read from one table and update another. This is very posible to do, and in fact there are a few ways to go about it. I don't see enough details to indicate exactly what you need, but I'll show you a few examples of things you can do. This will give you a few ideas to either figure out what you want, or ask another question, or perhaps another forum reader can add to this.

-- Example 1 -----
-- Updating by joining to another table
Update Orders o
set MainOrderNo = x.AnotherColumn
From Orders o
Inner join OtherTable x
ON o.Column = x.Column

-- Example 2 -----
-- Using a subquery in a set clause
Update Employees
SET job_level =
(SELECT max_level FROM Jobs
WHERE Employees.job_id = Jobs.job_id)
WHERE hire_date LE '6/30/1990'


-- Example 3 -----
-- Using a subquery in the where clause
Update Employees
SET job_level = job_level + 10
WHERE pub_id =
(Select pub_id FROM Publishers
INNER JOIN Titles On Publishers.pub_id = Titles.pub_id
WHERE type LIKE '%computer%)
--------------------------------
Not sure if this helps. Hopefully gets you started.

bperry

 

[Mute101]

OK sorry I found it difficult explaining myself for this post here is the acces code that I want to change to T-SQL.

Set Rs = New ADODB.Recordset
Rs.Open "SELECT [Purchase Order Number],[Required Delivery Date],_
despatchdate,shiptown,noofboxes,MainOrderNo,OrderNo,_
ConsignmentNo,DeliveredSignee,DeliveredDate,DeliveredTime FROM TempParity WHERE MainOrderNo is NOT NULL", adoCN, adOpenKeyset, adLockPessimistic
If Rs.RecordCount <> 0 Then
    Rs.MoveFirst
    Do Until Rs.EOF
        strSQLSearch = &quot;SELECT ConsignmentNo,DeliveredSignee,DeliveredDate,DeliveredTime FROM Orders WHERE OrderNo = &quot; & Rs!MainOrderNo
        Set RsSearch = New ADODB.Recordset
        RsSearch.Open strSQLSearch, adoCN, adOpenKeyset, adLockOptimistic
        If RsSearch.RecordCount <> 0 Then
            Rs!ConsignmentNo = RsSearch!ConsignmentNo
            Rs!DeliveredSignee = RsSearch!DeliveredSignee
            Rs!DeliveredDate = RsSearch!DeliveredDate
            Rs!DeliveredTime = RsSearch!DeliveredTime
            Rs.Update
        End If
        RsSearch.Close
        Set RsSearch = Nothing
        Rs.MoveNext
    Loop
Rs.Close
Set Rs = Nothing

I think you previous post has given me an idea of the way to go but if you can clarify anything from this code please post away.

Sim

 

[bperry]

Hi there.
I see. You want to update 4 columns in table TempParity (when MainorderNo is not null) with the matching columns from the Order table. Right?

You can try this.
It's untested, so don't use on a live system until you feel sure it works.
-------------------------
Update TempParity t SET
ConsignmentNo = o.ConsignmentNo,
DeliveredSignee = o.DeliveredSignee,
DeliveredDate = o.DeliveredDate,
DeliveredTime = o.DeliveredTime
From TempParity t
Inner Join Orders o
ON t.MainOrderNo = rderNo
WHERE t.mainOrderNo IS NOT NULL
--------------------------

But it should work okay. let me know.

rgrds, etc
bperry

 

[Mute101]

Ugh!

Sorry for the ugly code, it boils down to replacing four fields with relevant information taken from the Main Order.

Looking at your examples I can use example 2 with a subquery for each of the four SET operations. My next question would be is this more efficient than using the clientside code posted above?

Thanks.

Ps. learn from my mistakes NEVER take over a bespoke package written in-house from a hobbyist programmer!!! - See the field names I have to cope with!

 

[Mute101]

Thanks I think we double posted i got your response as I clicked on the submit. Please ignore my above post.

I will go try your code now.

Thanks very much for the fast response.

 

[bperry]

Okay, you are going to get a syntax error.
Please omit the alias t on the very first line.

Update TempParity SET
ConsignmentNo = o.ConsignmentNo,
DeliveredSignee = o.DeliveredSignee,
DeliveredDate = o.DeliveredDate,
DeliveredTime = o.DeliveredTime
From TempParity t
Inner Join Orders o
ON t.MainOrderNo = rderNo
WHERE t.mainOrderNo IS NOT NULL

 

[Mute101]

Brilliant.

Thanks for the code, as you can see I have a lot to learn in T-SQL.

I figured out the alias problem from BOL which has become my new best friend.

Thanks again it works a treat now.

Sim

 

[bperry]

>>I figured out the alias problem from
>>BOL which has become my new best friend.
Good, that's great.
And this should perform much better than the old code.

rgrds, etc
bp