October 6, 2008 at 1:11 pm
Hi. I am currently making a stored procedure for our helpdesk to find duplicate users, update them as inactive, then update their incidents to their active id, then delete them from the system. My update queries work fine, but I need to put an IF statement in the code. See we have requestors and customers in our system, sometimes they are the same and sometimes they are different people so I need to differentiate between them using an IF statement. Check out my code below, any assistance would be appreciated as I can't get this to work. I have tried multiple ways to get this work.
CREATE PROCEDURE dbo.UpdateuserID
@TNum as int
AS
-- Need to first update contact to inactivate MAX id value
SET @TNum = (SELECT number From workitem wi
inner join
(
Select id, name
from contact
where status = 'i'
group by name, id
)c
on c.id = wi.contact_id
Inner join
(
select name, id
from contact
where status = 'a'
group by name, id
)c2
on c.name = c2.name
where c.id<>c2.id)
UPDATE t1
SET
t1.status = 'i'
, t1.modified_on = GETDATE()
FROM CONTACT t1
INNER JOIN
(
SELECT
name
, MIN(id) AS id
FROM CONTACT
WHERE status = 'a'
GROUP BY name
HAVING COUNT(*) > 1
) t2
ON t1.name = t2.name
AND t1.id <> t2.id
WHERE t1.status = 'a'
IF ((SELECT wi2.customer_id
FROM workitem wi2
WHERE wi2.number=@TNum)
=
(SELECT wi3.contact_id
FROM workitem wi3
WHERE wi3.number=@TNum))
BEGIN
UPDATE wi
SET
wi.contact_id = c2.id,
wi.customer_id=c2.id
From workitem wi
inner join
(
Select id, name
from contact
where status = 'i'
group by name, id
)c
on c.id = wi.contact_id
Inner join
(
select name, id
from contact
where status = 'a'
group by name, id
)c2
on c.name = c2.name
where c.id<>c2.id
END
ELSE
BEGIN
--Then update the workitem tables with the active id value
UPDATE wi
SET
wi.contact_id = c2.id
From workitem wi
inner join
(
Select id, name
from contact
where status = 'i'
group by name, id
)c
on c.id = wi.contact_id
Inner join
(
select name, id
from contact
where status = 'a'
group by name, id
)c2
on c.name = c2.name
where c.id<>c2.id
END
-- Then delete the inactive id value from the contact table
delete
from contact
where status = 'i'
GO
October 6, 2008 at 2:11 pm
What's it doing that it shouldn't be or not doing what it should?
Basically, can you give us a little more detail on what's wrong?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 5:23 am
I don't know if I can do what I want it to do the way it is setup. I have an incident database for our helpdesk and I am trying to remove the duplicate users. Now in removing the user, I have to update the ticket table with the active customer ID number and find out if the customer is also the contact, if so, then I have to update the contact ID as well. I think instead of an IF statement for this I just need to make one update query for customer, then another update query for contact rather than trying to do it all in one query. But any other suggestions would be appreciated.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply