The IN Word - 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)

The IN Word

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-06 : 10:32:20

This statement finds any occurence of the string 'tid=' in the Querystring column and then copies it to the TID column. BUT what I want to know how to do is to FIRST lookup in another table if the TID that I find exists?

I know it must be the IN keyword but don't know how to implement in this statement.

Thanks


UPDATE W 
	SET W.TID = theTID
	FROM WebLog W 
	INNER JOIN 
	 (SELECT
	   IP,
	   SUBSTRING(Querystring,CHARINDEX('?tid=',Querystring) + 5,8000)  as theTID
	  FROM WebLog l
	  WHERE l.Querystring LIKE '%tid=%'
	    AND [Date] =
	     (SELECT Max([Date])
	      FROM WebLog
	      WHERE IP=l.IP)) as Y 
	  ON W.IP=Y.IP
	WHERE W.Querystring LIKE '%tid=%'

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-02-06 : 10:38:20

UPDATE W
SET W.TID = theTID
FROM WebLog W
INNER JOIN
(SELECT
IP,
SUBSTRING(Querystring,CHARINDEX('?tid=',Querystring) + 5,8000) as theTID
FROM WebLog l
WHERE l.Querystring LIKE '%tid=%'
AND [Date] =
(SELECT Max([Date])
FROM WebLog
WHERE IP=l.IP)) as Y
ON W.IP=Y.IP
WHERE W.Querystring LIKE '%tid=%'
AND EXISTS(SELECT * FROM YourTable WHERE YourTID = Y.TheTID)

Sarah Berger MCSD

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-06 : 11:17:43

quote:

UPDATE W
SET W.TID = theTID
FROM WebLog W
INNER JOIN
(SELECT
IP,
SUBSTRING(Querystring,CHARINDEX('?tid=',Querystring) + 5,8000) as theTID
FROM WebLog l
WHERE l.Querystring LIKE '%tid=%'
AND [Date] =
(SELECT Max([Date])
FROM WebLog
WHERE IP=l.IP)) as Y
ON W.IP=Y.IP
WHERE W.Querystring LIKE '%tid=%'
AND EXISTS(SELECT * FROM YourTable WHERE YourTID = Y.TheTID)

Sarah Berger MCSD

Thanks but it kinds doesn't work.

If I introduce a TID that doesn't exist in the lookup table then the SQL still places the TID in the column.

Why?


UPDATE W 
	SET W.TID = theTID
	FROM WebLog W 
	INNER JOIN 

	 (SELECT
	   IP,
	   SUBSTRING(Querystring,CHARINDEX('?tid=',Querystring) + 5,8000)  as theTID
	  FROM WebLog l
	  WHERE l.Querystring LIKE '%tid=%'
	    AND [Date] =
	     (SELECT Max([Date])
	      FROM WebLog
	      WHERE IP=l.IP)) as Y 

	ON W.IP=Y.IP
	WHERE W.Querystring LIKE '%tid=%'
	AND EXISTS(SELECT TrackID FROM TheUserDetails WHERE TrackID = Y.TheTID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources