Execute only IF it doesn't exist already? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Execute only IF it doesn't exist already?

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 12:48:08

Is there a way I can run this sql only IF the conditions haven't already been previously met?


UPDATE W 	
SET PageRequested=PageRequested + '?tid=somenumber' 
FROM WebLog W INNER JOIN 
	(SELECT IP, Min(ID) AS MinID FROM WebLog 
	WHERE [Date]=(SELECT max([Date]) FROM WebLog) 
	AND CHARINDEX('tid=', PageRequested) = 0
	GROUP BY IP)
as Y 
ON W.ID=Y.MinID

well what happens is that the first time it is ran, it updates the first occurence of that IP for that day. But if it runs again then it updates the second occurence of that IP for that day.

I want it to say "Hey, I have already altered this guy for today, ignore him."

Edited by - jesus4u on 01/28/2003 13:03:38

Edited by - jesus4u on 01/28/2003 13:04:36

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-28 : 13:18:48

You can add a WHERE clause you your update statement:

UPDATE W
SET PageRequested=PageRequested + '?tid=somenumber'
FROM WebLog W INNER JOIN
(SELECT IP, Min(ID) AS MinID FROM WebLog
WHERE [Date]=(SELECT max([Date]) FROM WebLog)
AND CHARINDEX('tid=', PageRequested) = 0
GROUP BY IP)
as Y
ON W.ID=Y.MinID
WHERE PageRequested Not Like '%' + '?tid=somenumber'

You could also add an "Updated" field that you could update when you do this update statement and use that field to check, that would be much faster because the WHERE clause I gave you can't use an index.

That is, something like

UPDATE W
SET PageRequested=PageRequested + '?tid=somenumber', Updated = 1
FROM WebLog W INNER JOIN
(SELECT IP, Min(ID) AS MinID FROM WebLog
WHERE [Date]=(SELECT max([Date]) FROM WebLog)
AND CHARINDEX('tid=', PageRequested) = 0
GROUP BY IP)
as Y
ON W.ID=Y.MinID
WHERE Updated = 0

- Jeff

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 13:26:09

Yes that is a great idea but unfortunately that would update the next occurrence of that IP for that day/....

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-28 : 14:01:13

add a where not exists clause to check if an entry already exists.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 14:08:09

quote:

add a where not exists clause to check if an entry already exists.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

i AM NOT SURE HOW TO DO THAT.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-28 : 14:23:09

You need to take away some criteria from your inner query. You don't want to the check for first one that day that already has been updated, you want to just check the first one for that day and check if it has been updated already.

Make sense?

Try:

UPDATE W
SET PageRequested=PageRequested + '?tid=somenumber'
FROM WebLog W INNER JOIN
(SELECT IP, Min(ID) AS MinID FROM WebLog
WHERE [Date]=(SELECT max([Date]) FROM WebLog)
GROUP BY IP)
as Y
ON W.ID=Y.MinID
WHERE
CHARINDEX('tid=', PageRequested) = 0

- Jeff

Edited by - jsmith8858 on 01/28/2003 14:25:35

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 14:31:49

sorry but that just jumps to the next records for that day and alters it. I need to completely ignore all the rest of the records for that IP and Day...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources