October 23, 2006 at 3:16 pm
I have a stored proc. it is updating all the rows in the UserRoles table instead of only 1 row.
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
October 23, 2006 at 4:09 pm
I don't see ur paramter being used to filter any data(Its not in where clause). Looks like u are using it to decide what the value of status is going to be (not whats being filtered) and that could be why its updating all the rows.
what does the follwoing query give:
select A.* 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
These are all the records that will be affected everytime.
October 24, 2006 at 9:43 am
When i run this query i get only 1 row. The user table has three rows for for eachid. The first rowid is always 1 the second rowid is always 2 and third one changes. The third one can be 3,4,8 10,11,12,13,14. So i need to update the third row. Thats why i use this and (RoleId = 8 or roleid=3 or roleid=4 or roleid=10 or roleid=11 or roleid=12 or roleid=13 or roleid=14) to find the row and update that row. It updates all the row of the table.
October 24, 2006 at 11:37 am
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.
October 24, 2006 at 12:30 pm
Your update is comparing one variable to another and never asks which record to update. As long as @status = 'Platinum' or 'gold' etc and @location is 'italy' or 'france' all records will be updated. Similar to "Update mytable set myfield = 0 where 1=1".
Case
when @status = 'platinum' and (@location ='italy' or @location ='france') then...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply