Need help with UPDATE & JOIN

[katbear]

Help. Can't figure this out. These queries *should* operate on the same number of rows but they don't:

-- updates 2 rows
begin tran
UPDATE t1
  SET t1.IndustryCode = t3.Industry
  FROM IndustryTable t1
  JOIN CompanyTable t2
    ON t1.IndustryID = t2.IndustryID
  JOIN CorrectTable t3
    ON t2.CompanyCode = t3.Symbol
  WHERE t1.IndustryCode <> t3.Industry
rollback

-- updates 35 rows!
SELECT
    t1.IndustryCode,
    t3.Industry
  FROM IndustryTable t1
  JOIN CompanyTable t2
    ON t1.IndustryID = t2.IndustryID
  JOIN CorrectTable t3
    ON t2.CompanyCode = t3.Symbol
  WHERE t1.IndustryCode <> t3.Industry

And I can't figure out what I'm doing wrong here.

Thanks

 

[katbear]

Correction: I mean the second one SELECTS 35 rows!

 

[ESquared]

A select returns all rows after the join. Update operates only one one table in the join. If the update supplies multiple values from another table for the updated table, the updated table will still only be updated once.

From what you've told us, I can tell that your industry table has only 2 rows that are being updated, and there are 35 values from the join operation that you're trying to cram into those 2 rows. SQL Server picks an arbitrary row's value to use and only performs the update once for each row.

Look:
Table A
ID Value
1 W
2 R

Table B
ID Value
1 W
2 X
2 Y
2 Z

If you do an update joining table A to B on ID, and set table A's Value to B's Value where they don't match, what will the server do? It can't update Table A's ID 2 to three values (X, Y, Z), so you get one of those values, arbitrarily (perhaps X).

When you select based on a join between these two tables on those conditions, you'd get three rows, but an update would only update one row.


[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[katbear]

How can I get this to work?

It's very confusing to me.

When I run the update statement, it updates 2 rows.
When I run the select, it returns 35 rows.

If I run the update statement again, it updates 35 rows.
When I run the select again, it returns 2 rows!

Something weird and circular is going on here.

 

[ESquared]

If you want help you'll need to give samples of your data: what it's like, what you want it to look like.

Obviously something cyclic is occurring. I don't think there's anything weird about it. It's just a side effect of the data and the join criteria.

If you put a subset (or all) of your data into Excel and examine it REALLY CAREFULLY you will surely figure out what is going on.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[katbear]

Ok, thanks - I will investigate further!