CODEWITHSUNDEEP
sqlsql-server
joncodo
joncodo

Reputation: 2328

sql query updates all rows and not only where clause

I am having trouble executing this query:

update public.fortune_companies 
set industry_id = (select id 
                   from public.industries 
                   where name = 'Agriculture, Forestry and Fishing') 
from Temp_Sic_Fortune_Companies as temp 
left join public.fortune_companies as fc on fc.account_name = temp.account_name
where temp.sic between '0' and '499';

I think this is supposed to set the industry_id for only ones that have a sic number of 0-499 but it actually sets every record to the same id. No matter if the sic number is between 0-499 or not.

Why is this.

Upvotes: 0

Views: 131

Answers (2)

anon
anon

Reputation: 

DECLARE @id INT;

SELECT @id = id 
  FROM public.industries 
  WHERE name = 'Agriculture, Forestry and Fishing';

UPDATE fc
  SET industry_id = @id
  FROM public.fortune_companies AS fc
  WHERE EXISTS
  (
    SELECT 1 
      FROM dbo.Temp_Sic_Fortune_Companies
      WHERE account_name = fc.account_name
      AND sic BETWEEN '0' and '499'
  );

Of course, if temp.sic = '3000', it will be part of the set. This is one of the dangers of using the wrong data type (or the wrong operator). You can fix that by saying:

AND sic BETWEEN '0' and '499'
AND LEN(sic) <= 3

Or by saying:

AND CASE WHEN ISNUMERIC(sic) = 1 THEN 
  CONVERT(INT, sic) ELSE -1 END BETWEEN 0 AND 499

(This avoids errors if - since you've let them - someone enters a non-numeric value into the column.)

Or by using the right data type in the first place.

Upvotes: 2

muhmud
muhmud

Reputation: 4604

Change the left join to inner join

Upvotes: 1

Related Questions