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

Tetol · Oct 4, 2020

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

carp · Oct 8, 2020

Try this:

Code:

Update table1
Set GroupName = (SELECT table2.GroupName2
                   From table2 
                  WHERE table1.AcctNum = table2.AcctNum 
                    AND table1.Location = table2.Location);

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!

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

Tetol

MIS

carp

MIS

Similar threads

Part and Inventory Search

Sponsor