I need to get a value from one database for a customer and update another database with that value.
The procedure below works but I need to have it go through table2 and update every customer in table1 with a matching CustomerID. I hate to use the word loop through but as I said, I am very new to this and lost. I have watched videos, and tried to search with no luck. Can someone point me to a tutorial or tell me if I am trying to do something I shouldn't be?
CREATE PROCEDURE dbo.bhshSample
as
BEGIN;
update table1 set
ptall = (
SELECT TOP (1) nsma1_ans
from table2
where nsma1_code = 'ptall'
order by nsma1_tm
)
where CustomerID = '4'
End;
In php, I would loop and do a select distinct CustomerID from table 2
then do the update using the variable I set but I can't seem to figure it out with stored procedure.
CodePudding user response:
Use a correlated subquery, like this:
update table1 set
ptall = (
SELECT TOP (1) nsma1_ans
from table2
where nsma1_code = 'ptall'
and CustomerID = table1.CustomerID
order by nsma1_tm
)