Delete result from Select?

Delete result from Select?

Post by Tibor Karasz » Wed, 05 Dec 2001 22:30:30



Jonah,

Can you elaborate a bit on what you want to do?

A SELECT statement returns data. It is a bit like doing a message box in VB and wanting to
delete that. There's nothing to DELETE... Unless you want to delete based on the same criteria
in the SELECT? Then just form the delete statement on that:

DELETE FROM         discodreams_com.SessionData
WHERE     (sessionvaluename = 'name') AND (DATEDIFF(second, sessionDate,
GETDATE()) > 30)

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:> Hi guys!

> How can I delete the results of the following Select-statement for use in a
> trigger?

> SELECT     *
> FROM         discodreams_com.SessionData
> WHERE     (sessionvaluename = 'name') AND (DATEDIFF(second, sessionDate,
> GETDATE()) > 30)

> /Jonah

 
 
 

Delete result from Select?

Post by Jonah Olsso » Wed, 05 Dec 2001 22:26:08


Hi guys!

How can I delete the results of the following Select-statement for use in a
trigger?

SELECT     *
FROM         discodreams_com.SessionData
WHERE     (sessionvaluename = 'name') AND (DATEDIFF(second, sessionDate,
GETDATE()) > 30)

/Jonah

 
 
 

Delete result from Select?

Post by Jonah Olsso » Wed, 05 Dec 2001 22:48:33


Hi Tibor,

Yep, I thought so, but all I get it this error message when using the delete
statement:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Maximum stored procedure, function, trigger, or view nesting level exceeded
(limit 32).

What's wrong?
/Jonah



Quote:> Jonah,

> Can you elaborate a bit on what you want to do?

> A SELECT statement returns data. It is a bit like doing a message box in
VB and wanting to
> delete that. There's nothing to DELETE... Unless you want to delete based

on the same criteria
Quote:> in the SELECT? Then just form the delete statement on that:

> DELETE FROM         discodreams_com.SessionData
> WHERE     (sessionvaluename = 'name') AND (DATEDIFF(second, sessionDate,
> GETDATE()) > 30)

 
 
 

Delete result from Select?

Post by Steve Kas » Wed, 05 Dec 2001 22:59:23


Jonah,

  You must have a delete trigger on discodreams_com.SessionData
that either initiates nesting or recursion or calls a procedure or
function that does, and that recursion/nesting gets out of hand.  Can you
provide the DDL for the trigger?

Steve


> Hi Tibor,

> Yep, I thought so, but all I get it this error message when using the delete
> statement:

> Microsoft OLE DB Provider for SQL Server (0x80040E14)
> Maximum stored procedure, function, trigger, or view nesting level exceeded
> (limit 32).

> What's wrong?
> /Jonah



> > Jonah,

> > Can you elaborate a bit on what you want to do?

> > A SELECT statement returns data. It is a bit like doing a message box in
> VB and wanting to
> > delete that. There's nothing to DELETE... Unless you want to delete based
> on the same criteria
> > in the SELECT? Then just form the delete statement on that:

> > DELETE FROM         discodreams_com.SessionData
> > WHERE     (sessionvaluename = 'name') AND (DATEDIFF(second, sessionDate,
> > GETDATE()) > 30)

 
 
 

Delete result from Select?

Post by Jonah Olsso » Thu, 06 Dec 2001 00:29:01


Steve,

Forgot that I already had a trigger that removes posts older than 30
minutes.

CREATE Trigger tr_destroyexpiredsessions
On discodreams_com.SessionData
For Insert, Update, Delete
As
delete from sessionData where datediff(minute, sessionDate,getDate()) > 30

The next trigger on the same table executes:

CREATE Trigger tr_destroyexpiredsessions2
On discodreams_com.SessionData
For Insert, Update, Delete
As
DELETE FROM discodreams_com.SessionData WHERE (sessionvaluename = 'name')
AND (DATEDIFF(second, sessionDate, GETDATE()) > 30)

I believe I have to rethink the session handling. The last trigger is for a
message function on the site. A 0 pix frame updates every 15 seconds and
checks the DB for new messages. But the problem was that many users tried to
send messages to users no longer active on the site (the session hadn't
timed out). So I thought I could use this file to update a session every 15
seconds and when the session no longer updates, the user has probably left
the site and the session is being deleted.

/Jonah



> Jonah,

>   You must have a delete trigger on discodreams_com.SessionData
> that either initiates nesting or recursion or calls a procedure or
> function that does, and that recursion/nesting gets out of hand.  Can you
> provide the DDL for the trigger?

> Steve


> > Hi Tibor,

> > Yep, I thought so, but all I get it this error message when using the
delete
> > statement:

> > Microsoft OLE DB Provider for SQL Server (0x80040E14)
> > Maximum stored procedure, function, trigger, or view nesting level
exceeded
> > (limit 32).

> > What's wrong?
> > /Jonah

> > "Tibor Karaszi"


- Show quoted text -


> > > Jonah,

> > > Can you elaborate a bit on what you want to do?

> > > A SELECT statement returns data. It is a bit like doing a message box
in
> > VB and wanting to
> > > delete that. There's nothing to DELETE... Unless you want to delete
based
> > on the same criteria
> > > in the SELECT? Then just form the delete statement on that:

> > > DELETE FROM         discodreams_com.SessionData
> > > WHERE     (sessionvaluename = 'name') AND (DATEDIFF(second,
sessionDate,
> > > GETDATE()) > 30)

 
 
 

1. Different results with SELECT SQL and DELETE SQL.

To all VFP gurus.

I have 2 sql statements which produce different results with the same filter
clause:
The delete statement deletes more records than the select returned.

select * from table_a ;
 where keyfield_a in (select keyfield_a from table_b) and ;
 keyfield_a not in (select keyfield_a from table_c)

delete from table_a ;
 where keyfield_a in (select keyfield_a from table_b) and ;
 keyfield_a not in (select keyfield_a from table_c)

Has anyone seen this?

TIA

Regards
JL

2. SQL7 won't transfer db without errors??

3. How can i make select that will return only 20 first results instead of all results

4. overflow (desbordamiento)

5. SELECT statement and view with same select produce different results

6. of subject: database or banking post?

7. select count(*) and select * return incoherent result

8. Strange D3 error message.

9. Result sets using select in Query Anlyzer vs BCP vs Select Into

10. question regarding select (multiple rows select into one result row)

11. SELECT statement and view with same select produce different results

12. Delete from ServerB using ServerA results?

13. Views do not display results when the columns of the table are deleted