Update only once - SQL Server Forums

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)

Update only once

Author

Topic

chrisww2
Starting Member

4 Posts

Posted - 2005-12-19 : 10:41:45

When I run this sproc I do not want to reupdate participants if they have already been updated to 2? Right now it is going thru and re-updating the particpant everytime it runs. I want it to just update people once, if they have already been updated....Any help is much apperciated.

Thanks

Update Participant
set Mail_Opt_Status = 2,
Mail_Date_Opt_Out = cast(convert(varchar(25), Getdate(), 102) as datetime),
Phone_Opt_Status = 2,
Phone_Date_Opt_Out = cast(convert(varchar(25), Getdate(), 102) as datetime),
Email_Opt_Status = 2,
Email_Date_Opt_out = cast(convert(varchar(25), Getdate(), 102) as datetime),
Fax_Opt_Status = 2,
Fax_Date_Opt_Out = cast(convert(varchar(25), Getdate(), 102) as datetime)
From Participant p
inner
join Participant_Answers pa
on p.person_id = pa.person_id
Where Mail_Opt_Status <> 2
and ((pa.campaign_id = 65
and pa.answer_id in (1000, 801))
or (pa.campaign_id = 59
and pa.answer_id in (2360, 2361, 2362, 2363, 2364))
or (pa.campaign_id = 63
and pa.answer_id in(2385, 2386, 2387, 2388, 2389, 2498))
or (pa.campaign_id = 39
and pa.answer_id in(2452)))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-19 : 13:17:37

I think something else must be going on. Your criteria seems to work (doesn't update rows where Mail_Opt_status = 2).

I pasted your WHERE criteria and applied it to some sample data. Only the non "2" value was updated:
(I reformated the where criteria just so I could read it)


set nocount on
declare @tb table 
	(mail_Opt_status int
	,campaign_id int
	,answer_id int)

insert @tb values (1,65,801)
insert @tb values (2,65,801)

select * from @tb

update pa set
	mail_opt_status = 2
from	@tb pa
Where	Mail_Opt_Status <> 2
and	(
		   (pa.campaign_id = 65 and pa.answer_id in (1000, 801)) 
		or (pa.campaign_id = 59 and pa.answer_id in (2360, 2361, 2362, 2363, 2364)) 
		or (pa.campaign_id = 63 and pa.answer_id in (2385, 2386, 2387, 2388, 2389, 2498)) 
		or (pa.campaign_id = 39 and pa.answer_id in (2452))
	)

select @@rowcount [rowsUpdated] --Should be 1
select * from @tb

Be One with the Optimizer
TG

chrisww2
Starting Member

4 Posts

Posted - 2005-12-19 : 14:12:15

Yeah, I think what was wrong is that it was still updating my other fields so I ended up doing whaat I highlighted below. If you know of a more efficient way of doing this please let me know. Thank you for looking at the code.

Update Participant
set Mail_Opt_Status = 2,
Mail_Date_Opt_Out = cast(convert(varchar(25), Getdate(), 102) as datetime),
Phone_Opt_Status = 2,
Phone_Date_Opt_Out = cast(convert(varchar(25), Getdate(), 102) as datetime),
Email_Opt_Status = 2,
Email_Date_Opt_out = cast(convert(varchar(25), Getdate(), 102) as datetime),
Fax_Opt_Status = 2,
Fax_Date_Opt_Out = cast(convert(varchar(25), Getdate(), 102) as datetime)
From Participant p
inner
join Participant_Answers pa
on p.person_id = pa.person_id
Where Mail_Opt_Status <> 2 and Email_Opt_Status <> 2 and Fax_Opt_Status <> 2
and ((pa.campaign_id = 65
and pa.answer_id in (1000, 801))
or (pa.campaign_id = 59
and pa.answer_id in (2360, 2361, 2362, 2363, 2364))
or (pa.campaign_id = 63
and pa.answer_id in(2385, 2386, 2387, 2388, 2389, 2498))
or (pa.campaign_id = 39
and pa.answer_id in(2452)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources