Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update script is changing the number of records for the values updated each time I run the query

Status
Not open for further replies.

Tetol

MIS
Dec 21, 2010
26
0
0
US
I have a a query result set in a temp table1 with 5 columns.
AcctNum, Location, GroupName, Count1, Count2. Columns Count1 and Count tow has 1s and nulls in it all the way through. I intend to sum these columns and group by Location, GroupName in a later step.

I need to update the GroupName column in table1 from another temp table2 where I have Distinct records of 3 columns. AcctNum, Location, GroupName2.

My update script is:

Update table1
Set GroupName = table2.GroupName2
From table1 Left Outer Join
table2 on table1.AcctNum = table2.AcctNum AND table1.Location = table2.Location

However, the number of occurrences of 1s in column1 and column2 keep changing for those records that were updated by the update script each time I run the script to validate the numbers. I am not sure what this keeps happening. Any Thoughts? see attached for sample tables and expected result without change to column1 and column2 values.

Thank you
Screen_shot_plikay.png
 
Try this:
Code:
Update table1
Set GroupName = (SELECT table2.GroupName2
                   From table2 
                  WHERE table1.AcctNum = table2.AcctNum 
                    AND table1.Location = table2.Location);
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top