asked
Why would a update effect less rows than the same select query?
I have two tables, and I want to update values in the first one based on a inner join with values from the second table (seems pretty simple right?). Here is the thing I can't figure out; If I do a select on the data fields I want to update (and be updated from) I get 24187 rows. When I do the update, it says 8097 rows affected. Here is the simplified tables and code: [dbo].[table1]( [IDT#] [int] IDENTITY(1,1) NOT NULL, [MiddleName] [varchar](30) NOT NULL ) [dbo].[table2]( [RecordID_PK] [int] IDENTITY(1,1) NOT NULL, [ID_NUMBER] [int] NOT NULL, [MiddleName] [varchar](30) NOT NULL ) Now I want to update table1's MiddleName field with the value of the MiddleName field from table2; The IDT# corresponds to the ID_NUMBER (I didn't design it, I just have to support it!). So here is what happens: SELECT t1.MiddleName, t2.MiddleName FROM [dbo].[table1] t1 INNER JOIN [dbo].[table2] t2 ON t1.IDT# = t2.ID_NUMBER 24187 rows Now I do the update: UPDATE [dbo].[table1] SET [dbo].[table1].[MiddleName] = [dbo].[table2].[MiddleName] FROM [dbo].[table1] INNER JOIN [dbo].[table2] ON [dbo].[table1].[IDT#] = [dbo].[table2].[ID_NUMBER] (8097 row(s) affected) I know the IDT# is unique because of the PK constraint. I ran a SELECT DISTINCT [dbo].[table2].[ID_NUMBER] FROM [dbo].[table2] and I get 58812 rows. Again, when I do the select joining on the column I get 24187 rows. The other weird thing is, it looks like the update commands works for all the records; when I look at the select query, all the values from the middle name column match. What am I missing?
The reason for less number of rows getting updated is due to Table1, having less number of rows(8097 rows). When you do select on idnumber, even though table1 is having unique IDT#, table2 is having duplicate id_number and many rows get selected in INNER JOIN (24187 rows). Please try below sample query to understand your problem. CREATE TABLE table1(idnumber int, middlename varchar(10)); CREATE TABLE table2(idnumber int, middlename varchar(10)); insert into table1 values(1,'a') insert into table1 values(2,'b') insert into table2 values(1,'a') go 10 insert into table2 values(2,'b') go 10 SELECT t1.middlename, t2.middlename from table1 t1 join table2 t2 on t1.idnumber = t2.idnumber update t1 set t1.middlename = t2.middlename FROM table1 t1 join table2 t2 on t1.idnumber = t2.idnumber
15 People are following this question.
