Returning row value before deletion

I want to return a value from a row before i delete it. Is this
possible to do in one operation something like...
SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
RETURN t.columnValCJ wrote:

> I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
Not in one query but why don't you make a stored procedure that returns
the value(s) and deletes the rows?
Kind regards,
Stijn Verrept.|||Thanks Stijn-
This is running inside a stored proc. I have an output param that
needs to return a value from the record being deleted. I didn't want
to run both SELECT and DELETE against the table if there was a way of
capturing the record from the DELETE.
Regards,
-CJ|||in 2005 check out the OUTPUT clause.
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>|||Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on the
SELECT statement that precedes the DELETE.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
> Thanks Stijn-
> This is running inside a stored proc. I have an output param that
> needs to return a value from the record being deleted. I didn't want
> to run both SELECT and DELETE against the table if there was a way of
> capturing the record from the DELETE.
> Regards,
> -CJ
>|||Many thanks!
So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
in a transaction and the DELETE cascades over sevreal tables?|||Brian
>Be sure to use WITH(UPDLOCK)
I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
I remember our last discussion sometime ago where you explained why we need
to use HOLDLOCK hint in addition to UPDLOCK.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on
> the SELECT statement that precedes the DELETE.
> "CJ" <Charles.Deisler@.gmail.com> wrote in message
> news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
>|||Not prior to a DELETE. UPDLOCK is sufficient. UPDLOCK, HOLDLOCK is
necessary if there is a possibility of an INSERT or UPDATE on another
connection that could cause a PRIMARY KEY or UNIQUE constraint violation by
changing the value to one that is about to be INSERTed or UPDATEed.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eTAKC698FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Brian
> I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
> I remember our last discussion sometime ago where you explained why we
> need to use HOLDLOCK hint in addition to UPDLOCK.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||If you have cascading referential actions, then you have to obtain update
locks all affected rows in all affected tables. I don't like cascading
referential actions. I think they are a tool for the lazy or the
incompetent and cause more problems then they're worth. They can cause
locking order confusion and can increase the probability of deadlocks. It
is not possible to determine with certainty which objects will be locked in
which order if cascading referential actions are active. It's better to
write the code manually in the stored procedure that performs the DELETE
(this is the preferred method) or to use a trigger (preferrably an INSTEAD
OF trigger) because then you have complete control over the order in which
locks are obtained, thus eliminating the main cause of deadlocks that can be
avoided. There still exists the possibility of deadlocks, due to threading
or a poorly generated execution plan, but deadlocks caused by accessing
objects in the wrong order can be completely eliminated.
You are correct that the SELECT(s) and the DELETE must be wrapped in a
transaction. In addition, if you don't use WITH(UPDLOCK), two separate
transactions can obtain and hold shared locks on the same row such that
neither can obtain an exclusive lock in order to perform the DELETE, thus
causing a deadlock.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133153077.282668.76100@.g14g2000cwa.googlegroups.com...
> Many thanks!
> So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
> in a transaction and the DELETE cascades over sevreal tables?
>|||Would the Ouput clause not fit here?USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>