View Single Post - Update two tables using Stored Procedure in SQL

spinal007 · #4 (**permalink**) Jul 1st, 2007, 13:55

Ok, let me try and explain...

The 'WHERE' clause can be used in any T-SQL statement, whether it's within a trigger, stored procedure, function, etc...

I understand you have 2 tables, but what are their key fields? Is there an 'id' or 'payment_id' field?

Code: Select all

strSQL = "Update payments Set " & _
                "collegetotalpaid = 'Yes', " & _
                "collegetotalpaidDate = Convert(DateTime, GETDATE(), 103), " & _    
                "updated = Convert(DateTime, GETDATE(), 103), " & _
                "who_updated = '" & session("Name") & "' " & _
                "WHERE (payments.collegetotalpay = 'Yes') " & _
                "AND (payments.collegetotalpaid IS NULL)"

This code updates all records that match "payments.collegetotalpay = 'Yes' AND payments.collegetotalpaid IS NULL". This seems strange and it looks like you're missing a key field - ie.:
UPDATE ... WHERE payments.collegetotalpay = 'Yes' AND payments.collegetotalpaid IS NULL AND invoice_id = 9999

Code: Select all

 UPDATE payments 
    SET collegetotalpaid = @CollegeTotalPaid
    WHERE (payments.collegetotalpay = 'Yes') and (payments.collegetotalpaid IS NULL)
UPDATE paymentAdd 
    SET collegetotalpaid = @CollegeTotalPaid
    -WHERE (paymentAdd.collegetotalpay = 'Yes') and (paymentAdd.collegetotalpaid IS NULL)

This is updating every record because

First you update all records on the table payments that meet the 'where' clause
Then you update every record on the table paymentAdd (no where clause, no filtering, every record)

What you're missing is, like I said, a key field common to both tables. Surelly you must have one or the whole thing will make no sense and you won't no what record on the payments table all the data on the paymentsAdd table refers to.

When you've figure that out, add another parameter to your procedure like this...

Code: Select all

CREATE PROCEDURE sp_UpdatePaymentTable(
    @CollegeTotalPaid varchar (5), @InvoiceID int
) 
AS
BEGIN
 UPDATE payments 
    SET collegetotalpaid = @CollegeTotalPaid
    WHERE (collegetotalpay = 'Yes') and (collegetotalpaid IS NULL) AND invoice_id = @InvoiceID
UPDATE paymentAdd 
    SET collegetotalpaid = @CollegeTotalPaid
    WHERE (collegetotalpay = 'Yes') and (collegetotalpaid IS NULL) AND invoice_id = @InvoiceID


END

PS.: When a statement refers only to one table you don't need to keep repeating the table name. You can do this:
UPDATE payments SET amount = 9.99, date = getdate() WHERE payment_id = 9999
instead of...
UPDATE payments SET payments.amount = 9.99, payments.date = getdate() WHERE payments.payment_id = 9999