Update Table A (only 4 rows) with data from table B

[Glowworm27]

I have 2 tables
Guaranteed_Draw_Master and Guaranteed_Draw_History.
The tables are relational based on the Universal_ID, and the Sequence_Nbr There is also a Column in the Master table that tells me which rows are Active, (Active_Ind='A')

I updated some rows in a program and it creates History Rows, and need a way to set these back to in case something goes wrong. And then delete the history rows.

So I want to write a procedure to that will put the data back to before the program ran, also based on the current Check Week.

my trouble is the Sql I first wrote would try to insert Null value into the Draw_to_Date_Amt which is not allowed, So I think it was trying to update all the rows in the master table, but I only want to update the rows in the master table where the History rows exists for the current check week.

So I wrote this SQL and now I get an error that the subquery returns more than one value.

HEre is the Current SQL

Update Guaranteed_Draw_Master 
		Set DRAW_TO_DATE_AMT = (Select H.DRAW_TO_DATE_AMT From Guaranteed_Draw_History H 
								Inner Join Guaranteed_Draw_Master M On H.UNIVERSAL_ID = M.UNIVERSAL_ID 
								And H.SEQUENCE_NBR = M.SEQUENCE_NBR 
								Where H.PAY_DATE = oCheckDate -6 
								And M.Active_Ind='A')
		Where Exists (select 1 from Guaranteed_Draw_Master 
						Inner join Guaranteed_Draw_History on Guaranteed_Draw_Master.UNIVERSAL_ID = Guaranteed_Draw_History.UNIVERSAL_ID 
						and Guaranteed_Draw_Master.SEQUENCE_NBR = Guaranteed_Draw_History.SEQUENCE_NBR
						where Pay_Date = ocheckDate -6 and Active_Ind = 'A')
		;

IF some one could look at this and show me why the query wont work. I would greatly appreciate it.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

I was Close, I got it working now
the answer is this

Update Guaranteed_Draw_Master M 
		Set DRAW_TO_DATE_AMT = (Select H.DRAW_TO_DATE_AMT From Guaranteed_Draw_History H 
								Where H.UNIVERSAL_ID = M.UNIVERSAL_ID 
								And H.SEQUENCE_NBR = M.SEQUENCE_NBR 
								And H.PAY_DATE = oCheckDate -6 
								And M.Active_Ind='A')
		Where Exists (Select H.DRAW_TO_DATE_AMT From Guaranteed_Draw_History H 
								Where H.UNIVERSAL_ID = M.UNIVERSAL_ID 
								And H.SEQUENCE_NBR = M.SEQUENCE_NBR 
								And H.PAY_DATE = oCheckDate -6 
								And M.Active_Ind='A');

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[GeppoDarkson]

Hi,
take a look to "Merge into" command.