Update two tables using Stored Procedure in SQL

Wheatus7 · Jun 29th, 2007, 15:31

Is it possible to update two different tables with one stored procedure or would I need a sp and a trigger? Or would I need a stored procedure with a transaction?

I basically need to change similar fields in both tables from 'null' to 'Yes' - any ideas on best practice?

Many thanks

spinal007 · Jun 29th, 2007, 18:57

What's the exact situation? Is this a one off or something that will happen on a regular basis?

FACT: You can only update one table at a time (you can use views to give the impression that multiple tables are updated at once but we don't need to go there).

Trigger or SP?

A trigger is essentially a stored procedure that gets fired in response to an event (insert/update/detele).

But if you use a stored procedure you will need to 'manually' call it after updating one of the tables.

Just use whatever method you're more comfortable with.

How do you do it?

PROCEDURE:
You'll need something like this...
Code: Select all
CREATE PROCEDURE dbo.UpdateTables(@value nvarchar(100)) AS
BEGIN
 UPDATE TABLE1 SET field1 = @value
 UPDATE TABLE2 SET field2 = @value
END
Add parameters and use the WHERE clause to update only certain records...

TRIGGER:
You will need something like...
Code: Select all
CREATE TRIGGER [TABLE1Trigger] ON [dbo].[TABLE1]
FOR INSERT, UPDATE, DELETE -- if applicable
AS
 UPDATE TABLE2 SET field2 = (SELECT field1 FROM TABLE1)
END
You actually have to use WHERE the clause in this case but you get the idea...

Hope this helps

Wheatus7 · Jul 1st, 2007, 11:20

Thanks for the response.

Could you guide me as to where to put the 'WHERE' clause?

I have tried this:

Code: Select all

CREATE PROCEDURE sp_UpdatePaymentTable(
    @CollegeTotalPaid varchar (5)
) 
AS
BEGIN
 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)


END

I have tried all combinations (ie either of the WHERE clauses or both and neither work). It just drops through ignoring the clause altogether and puts 'Yes' in every record.

Would it be easier to have an update sp on the payment table then a trigger on the paymentAdd table - if so can you use the WHERE clause in a trigger?

This action is not a one off and will be called many times.

My problem started with this query in my asp page - I have had to extend an application and have added an overflow table to catch additional data. Therefore both tables need to be updated at the same time:

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)"

Any help will be gratefully received as I am now over my deadline (hence the 6am Sunday morning start and stress levels reaching fever point!)

spinal007 · 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

Wheatus7 · Jul 1st, 2007, 16:59

Many thanks for your reply...putting both 'WHERE' clauses worked (strange they didn't work when I was trying my combinations - possibly blind panic and stress mixed in).

As your 'id' question - I am not using one because the call runs through the 'collegetotalpay' column looking for a 'Yes', if there are any then it writes in the 'collegetotalpaid' field 'Yes' (then a date) and the finance run proceeds on all of those records.

Hence, I am looking for all records not one specific invoice.

Thanks again for your help - onwards now to the asp part and setting values in parameters for the stored procedure to collect.

Does this EVER get easier because I honestly feel like changing professions!!

spinal007 · Jul 1st, 2007, 17:42

Quote:

Originally Posted by Wheatus7

Does this EVER get easier because I honestly feel like changing professions!!

lol... brace yourself my friend, it's only gonna get worse! - but you'll get used to it