Updating the OTHER record - 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)

Updating the OTHER record

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 14:12:03

ok.
AS this sql updates each IP address and finds the minimum ID number for that day's data without a TID, I also need it to find the occurence of a string 'anotherstring' is any of the records for that day and mark it.

--For each IP address, it finds the minimum ID number for that day's data without a TID...
--which translates to the "first" or "earliest" requested page.
--By JOINing this back to the WebLog table on the ID column,
--the UPDATE statement will change ONLY those ID's, representing the rows you want to modify.

DECLARE @strGUID uniqueidentifier

UPDATE W
SET PageRequested=PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))
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

Edited by - jesus4u on 01/28/2003 14:12:35

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 14:38:58

anyone???

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 15:53:39

So far this is what I have but it errors out saying:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'GROUP'.


SELECT PR = CASE WHEN SUBSTRING(PageRequested , 1, LEN(PageRequested)) like '%crmresourcesthankyou.asp%' THEN END
		SUBSTRING(PageRequested , 1, LEN(PageRequested)) as PR, Min(ID) AS MinID		
	FROM WebLog  
	WHERE  [Date]=(SELECT max([Date]) FROM WebLog)    
	GROUP BY PageRequested

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-28 : 16:16:02

I'm not sure if this will solve your solution, but i did see some typo's in your code:

 
SELECT 
CASE 
WHEN SUBSTRING(PageRequested , 1, LEN(PageRequested)) like '%crmresourcesthankyou.asp%' THEN '' 
END as PR,
SUBSTRING(PageRequested , 1, LEN(PageRequested)) as SubStrPR, 
Min(ID) AS MinID		
FROM WebLog  
WHERE  [Date]=(SELECT max([Date]) FROM WebLog)    
GROUP BY PageRequested

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 16:18:50

That definitly helped but then I realized that I need to do it in an UPDATE statement. Using this code as a starter. I am thinking that I can nest another SELECT...What do you think?
Thanks


	DECLARE @strGUID uniqueidentifier
	
	UPDATE W 	
	SET PageRequested=PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))
	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

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-28 : 16:21:20

Basically aS this sql updates each IP address and finds the minimum ID number for that day's data without a TID, I also need it to find the occurence of a string 'anotherstring' is any of the records for that day and mark it.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-01-29 : 02:14:38

Hi

How about this?

DECLARE @strGUID uniqueidentifier

UPDATE W
SET PageRequested=
CASE
WHEN PageRequested LIKE '%crmresourcesthankyou.asp%' THEN PageRequested
ELSE
PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))
END
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

I dont know if this is the solution to your problem, but i am guessing that you want to update this field only if it satisfies the condition, else we assign it to itself (which means it will not change)

OS

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-29 : 07:56:31

that is a good idea and I will try and let you know

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-29 : 09:57:50

quote:

Hi

How about this?

DECLARE @strGUID uniqueidentifier

UPDATE W
SET PageRequested=
CASE
WHEN PageRequested LIKE '%crmresourcesthankyou.asp%' THEN PageRequested
ELSE
PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))
END
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

I dont know if this is the solution to your problem, but i am guessing that you want to update this field only if it satisfies the condition, else we assign it to itself (which means it will not change)

OS

Well it was a nice try but it basically does what my code is already doing.

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-29 : 10:08:49

It just dawned on me that I need a way to pull back the MAX ID for the MIN ID. That will give me the string I am looking for.

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-29 : 10:16:46

Here is the data that needs to be transformed.

Take a look at IP 10.10.2.191. The code I already have adds the tid with the GUID that you see there. What I also need the code to do is seek out that last record 'crmresourcesthankyou.asp' and assign the SAME tid=52FC441. See what I mean?

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-01-29 : 11:18:39

Is this too hard for you guys here? I know it is for me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources