need to update 1 row but updating all rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need to update 1 row but updating all rows

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2006-10-23 : 16:53:12

I have a stored proc. it is updating all the rows in the UserRoles table instead of only 1 row. Currently the value of the roleid is 3 in the useroles table. I need to update with other roleid.

Here is the stored proc. i have hardcoded values in order to test it.
Declare @usersemail varchar(50)
declare @status varchar (50)
declare @location varchar (50)

set @status ='platinum'
set @location = 'france'

SELECT
@Status=part_status,
@location=loc_region,
@usersemail= SUBSTRING(loc_bus_email,
NULLIF(CHARINDEX('@', loc_bus_email), 0) + 1,
LEN(loc_bus_email) - CHARINDEX('@', loc_bus_email) + 1)

FROM LOCATION INNER JOIN PARTNER ON LOCATION.part_id = PARTNER.part_id where PARTNER.part_id ='350'

UPDATE UserRoles
SET RoleId = Case
when @Status = 'platinum' and (@location ='italy' or @location ='france') then 8
when @Status = 'gold' and (@location ='italy' or @location ='france') then 9
when @Status = 'silver' and (@location ='italy' or @location ='france') then 10

end
FROM
UserRoles As A
INNER JOIN users as B ON B.UserID = A.UserID

WHERE USERNAME like '%@dmi-fr.com' and (RoleId = 8 or roleid=3 or roleid=4 or roleid=10 or roleid=11 or roleid=12 or roleid=13 or roleid=14)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-23 : 17:22:23

It's not updating all the UserRoles, it's updating all the UserRoles that currently have a RoleId equal to one of the ones in your list, and which are UserRoles for Users with an email like '%@dmi-fr.com'. If that isn't what you want, then be more specific - if only one row should be updated, then you have to give a WHERE clause that is true for only that one row.

soorma
Yak Posting Veteran

52 Posts

Posted - 2006-10-23 : 17:44:50

I am kind of confused by ur reply.

This SQL gives me the status(gold, silver platinum) and location gives me(france, italy etc) and useremail gives me like(msn.com)
SELECT
@Status=part_status,
@location=loc_region,
@usersemail= SUBSTRING(loc_bus_email,
NULLIF(CHARINDEX('@', loc_bus_email), 0) + 1,
LEN(loc_bus_email) - CHARINDEX('@', loc_bus_email) + 1)

Then i use the status, location and email to update the userrole table. Instaed of updating one row it updates all the rows for the table.

When ever i run this statement it gives me only one row

select * FROM UserRoles As A INNER JOIN users as B ON B.UserID = A.UserID WHERE USERNAME like '%@dmi-fr.com' and (RoleId = 8 or roleid=3 or roleid=4 or roleid=10 or roleid=11 or roleid=12 or roleid=13 or roleid=14)

So something is worng in the case statement i think.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-23 : 17:56:20

Only the UserRoles row/s that are returned by

select * FROM UserRoles As A INNER JOIN users as B ON B.UserID = A.UserID WHERE USERNAME like '%@dmi-fr.com' and (RoleId = 8 or roleid=3 or roleid=4 or roleid=10 or roleid=11 or roleid=12 or roleid=13 or roleid=14)

will be updated when you run

UPDATE UserRoles
SET ...
FROM UserRoles As A INNER JOIN users as B ON B.UserID = A.UserID WHERE USERNAME like '%@dmi-fr.com' and (RoleId = 8 or roleid=3 or roleid=4 or roleid=10 or roleid=11 or roleid=12 or roleid=13 or roleid=14)

The CASE that you use in the SET part of the UPDATE has nothing to do with how many rows get updated. Check your proc again - you have a LIKE operator in your WHERE clause so that surely sometimes returns more than one User, which in turn causes more than one UserRole to be returned?

soorma
Yak Posting Veteran

52 Posts

Posted - 2006-10-24 : 13:37:34

I had to change my query. i used a subquery. But still i am baffeled why it was not working. i am using a link server to update probably thats why. It woked on the original database but not on the linked database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources