Maybe this makes more sense? 2

[Jesus4u]

Take a look at this pic and this is what I am trying to do. The code I have so far is below it.

Code:

UPDATE W 
	SET PageRequested = PageRequested + '?tid=' + CONVERT(varchar(250), LEFT(NEWID(), 7))
	FROM WebLog W INNER JOIN 
		(SELECT IP, (ID), SUBSTRING(PageRequested, CHARINDEX('?tid=', PageRequested), 50) as PR FROM WebLog 
		WHERE  [Date]=(SELECT max([Date]) FROM WebLog) AND PageRequested LIKE '%?tid=%'
		GROUP BY IP, (ID), PageRequested)
	as Y 
	ON W.ID=Y.ID
	WHERE PageRequested LIKE '%crmresourcesthankyou.asp%'

Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[Jesus4u]

Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[tlbroadbent]

Try this.

UPDATE W
SET PageRequested = PageRequested + Y.PR
FROM WebLog W
INNER JOIN
(SELECT
IP,
SUBSTRING(PageRequested,CHARINDEX('?tid=',PageRequested),50) as PR
FROM WebLog l
WHERE PageRequested LIKE '%?tid=%'
AND [Date] =
(SELECT Max([Date])
FROM WebLog
WHERE IP=l.IP)) as Y
ON W.IP=Y.IP
WHERE W.PageRequested LIKE '%crmresourcesthankyou.asp%'

You should test first by running the following select statment.

SELECT
ID, IP, [Date],
PageRequested + Y.PR As NewPR
FROM WebLog W
INNER JOIN
(SELECT
IP,
SUBSTRING(PageRequested,CHARINDEX('?tid=',PageRequested),50) as PR
FROM WebLog l
WHERE PageRequested LIKE '%?tid=%'
AND [Date] =
(SELECT Max([Date])
FROM WebLog
WHERE IP=l.IP)) as Y
ON W.IP=Y.IP
WHERE W.PageRequested LIKE '%crmresourcesthankyou.asp%'
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Jesus4u]

thanks but the second block of code produces the error:

Ambiguous column name 'IP'.

Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[SQLBill]

The message/error "Ambiguous column name 'IP'" usually is caused by a column being used in more than one table. You have a join of three tables and two of them have the column IP. SQL doesn't know for sure which one you want returned so it pops the error. Make these changes:

SELECT
ID, W.IP, [Date],
PageRequested + Y.PR As NewPR
FROM WebLog W
INNER JOIN
(SELECT
l.IP,
SUBSTRING(PageRequested,CHARINDEX('?tid=',PageRequested),50) as PR
FROM WebLog l
WHERE PageRequested LIKE '%?tid=%'
AND [Date] =
(SELECT Max([Date])
FROM WebLog
WHERE IP=l.IP)) as Y
ON W.IP=Y.IP
WHERE W.PageRequested LIKE '%crmresourcesthankyou.asp%'

I think that will work.

-SQLBill

 

[tlbroadbent]

Bill is correct. I forgot to qualify the column IP in the first select statement. It isn't necessary in the sub-qury but can be done. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Jesus4u]

THANK YOU THANK YOU!!! That is an amazing transformation.

I want to learn something today so can you explain the statement to me? Thanks Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[Jesus4u]

uh!?

When I insert the SELECT into the UPDATE I get the error:

Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '%crmresourcesthankyou.asp%'.

	UPDATE W 
	 SET PageRequested = PageRequested + Y.PR
	FROM WebLog W 
	INNER JOIN 
	 (SELECT
	 ID, W.IP, [Date],
	 PageRequested + Y.PR As NewPR
	FROM WebLog W 
	INNER JOIN 
	 (SELECT
	   l.IP,
	   SUBSTRING(PageRequested,CHARINDEX('?tid=',PageRequested),50) as PR 
	  FROM WebLog l
	  WHERE PageRequested LIKE '%?tid=%'
	    AND [Date] =
	     (SELECT Max([Date])
	      FROM WebLog
	      WHERE IP=l.IP)) as Y 
	  ON W.IP=Y.IP
	WHERE W.PageRequested LIKE '%crmresourcesthankyou.asp%'

Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[tlbroadbent]

Your syntax is incorrect. Use the UPDATE statement I provided in my first post. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Jesus4u]

tlbroadbent,

Why is it that the SELECT by itself looks different than the one in the UPDATE?

By the way, the UPDATE from your frist post DOES work beautifully!

Thank you and I voted for you too! Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[tlbroadbent]

I added columns to the select list in order to properly debug the query. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.