multiple queries (with variable?)

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

General SQL Server Forums

New to SQL Server Programming

multiple queries (with variable?)

Author

Topic

bernie
Starting Member

6 Posts

Posted - 2010-03-04 : 08:31:47

Hello,
I want to create a procedure or query that removes old entries from my DB. I have three tables. When deleting a row in table1, a score must descrease in table2 and possibly a row must be deleted in table3.

In detail I want to do this:

- delete * from ENTRIES WHERE post_datetime OLDER THEN 30DAYS AND provider <> 'local'
- decrease SCORES.score WHERE SCORES.tag = ENTRIES.tag from deleted row of the last query
- delete * from URLS WHERE url does not anymore exist in ENTRIES.url

My attempt:

DELETE * FROM ENTRIES WHERE datediff ('day', post_datetime, now()) > 30 AND provider <> 'local'

UPDATE SCORES SET score = score-1 WHERE ( tag = ????? )

DELETE * FROM URLS WHERE EXISTS (SELECT * FROM ENTRIES WHERE (URLS.url = ENTRIES.url))

My problem is query2. How can I get ENTRIES.tag from the deleted rows of query1, and use it in query2?

Or is there maybe a more elgant way to do what I want? Can I do all this in one single query?

Thanks a lot!
B

My DB tables:



create table ENTRIES
(
  "id" INTEGER IDENTITY,
  "tag" VARCHAR(255),
  "url" VARCHAR(255),
  "provider" VARCHAR(255),
  "post_datetime" DATETIME,
  PRIMARY KEY ("id")
);

create table SCORES
(
  "tag" VARCHAR(255),
  "score" INTEGER,
  "cur_timestamp" TIMESTAMP,
  PRIMARY KEY ("tag")
);

create table URLS
(
  "url" VARCHAR(255),
  "title" VARCHAR(255),
  "last_posted" DATETIME,
  PRIMARY KEY ("url")
);

quote:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 08:35:37

you need a trigger for deletion and do update inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 08:56:57

or you can use OUTPUT operator if in sql 2005


DECLARE @DELETED_TAGS table
(
Tag varchar(100)
)

DELETE e
OUTPUT DELETED.Tag INTO @DELETED_TAGS
FROM ENTRIES e 
WHERE e.post_datetime < DATEADD(dd,DATEDIFF(dd,0,GETDATE())-30,0) 
AND provider <> 'local'

UPDATE s
SET s.score = s.score-1 
FROM SCORES s
JOIN @DELETED_TAGS d
ON d.tag = s.tag

DELETE u FROM URLS u WHERE NOT EXISTS (SELECT 1 FROM ENTRIES WHERE (url = u.url))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bernie
Starting Member

6 Posts

Posted - 2010-03-08 : 04:24:02

Thanks,
I finally got it working!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 09:32:46

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources