Question re: doing an insert and delete

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)

Question re: doing an insert and delete

Author

Topic

soberhi
Starting Member

2 Posts

Posted - 2002-05-09 : 16:54:14

Hi. I am a newbie to this forum and am getting better at SQL, with plenty of room to improve.

I am looking for a clean way to do the following:

Table NotificationUser has many, many records of data.

I have a SELECT statement that retrieves data that I wish to INSERT/UPDATE into Table NotificationUser.

If the record (based upon the key) exists, UPDATE current record with new values. Otherwise do an insert.

Right now, I am using:

DECLARE @Checked

SELECT @Checked = 0

INSERT NotificationUser
SELECT @userID, NotificationID, @Checked
FROM Notification N
WHERE NotificationID NOT IN (
SELECT NU.NotificationID
FROM NotificationUser NU
WHERE NU.safranetuserID = @userID
)
AND startdate <= getdate()
AND expiredate >= getdate()

IF @Checked = 1
BEGIN
UPDATE NotificationUser
SET Viewed = 1
FROM Notification N
WHERE NotificationUser.safranetUserID = @userID
AND N.NotificationID = NotificationUser.NotificationID
AND NotificationUser.Viewed = 0
AND N.startdate <= getdate()
AND N.expiredate >= getdate()
END

Is this reasonable?

It seems to work, but is this a backwards way of doing it?

Thank you for reading this and any efforts to assist me are appreciated.

SoberHi

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-05-09 : 17:12:45

Seems to me that the update will never be run....

Better off doing something like the following: (Pseudo Code to get you started)

create proc <myproc>
@Oper char(1) output
, @userId uniqueidentifier output
as

-- I usually can skip the following block since I know the context
-- of whether the @Oper is 'I' or 'U'

-- Logic to find out if you are doing Insert or Update
if exists (SELECT NU.NotificationID
FROM NotificationUser NU
WHERE NU.safranetuserID = @userID)
begin
select @Oper = 'U'
end
else
begin
select @Oper = 'I'
end

--Now do the appropriate action (insert/update)
if @Oper = 'I'
if @UserId is null
begin
select @UserId = newid()
end
begin
--do insert
end
else
begin
--do update
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources