Re: Using Cursors

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nickl (Nickl_at_discussions.microsoft.com)
Date: 02/02/05 

Date: Tue, 1 Feb 2005 17:53:01 -0800

Thanks Tibor,
I am new to the "Exists" clause. I will try it out with all haste.
Cheers

"Tibor Karaszi" wrote:

> This seems to me like a simple question of a WHERE clause:
>
> UPDATE tbl1
> SET col1 = newvalue1, col2 = newvalue2
> WHERE EXISTS
> (SELECT *
> FROM tbl2
> WHERE refcol = tbl1.refcol)
>
> Or, using IN:
>
> UPDATE tbl1
> SET col1 = newvalue1, col2 = newvalue2
> WHERE refcol IN (SELECT refcol FROM tbl2)
>
> Same logic can be applied for DELETE.
>
> If you want to pick the values in SET from the other table, you have two options:
>
> ANSI standard SQL:
>
> UPDATE tbl1
> SET
> col1 = (SELECT col1 FROM tbl2 WHERE refcol = tbl1.refcol)
> ,col2 = (SELECT col2 FROM tbl2 WHERE refcol = tbl1.refcol)
> WHERE EXISTS
> (SELECT *
> FROM tbl2
> WHERE refcol = tbl1.refcol)
>
> SQL Server specific, update based on a JOIN:
>
> UPDATE tbl1
> SET col1 = tbl2.col1, col2 = tbl2.col2
> FROM tbl2 JOIN tbl1 ON tbl1.refcol = tbl2.refcol
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
>
> "Nickl" <Nickl@discussions.microsoft.com> wrote in message
> news:985B9DD9-B74E-4B23-9E62-E34433B45423@microsoft.com...
> > Hi David,
> > I have a question about how to replace a cursor with a set based operation.
> > If I want to UPDATE a subset of values in a table, based on a set of values
> > in another table, how can I do it without a cursor. The UDATE command only
> > lets me operate on one table, and this seems to exclude the join to the
> > reference table that I require.
> > Eg, Table1 has 3 records (out of many) I would like to change (or delete)
> > and are identified by a key field (order# or account# for example). Table2 is
> > the table that identifies those records in table1 that require changing.
> > I have been Looping through table1 with a cursor, and checking to see if
> > there is a match between the current record (in the cursor) with one of the
> > identifying records in table2. If there is a match, then I change (or delete)
> > the record before moving to the next record in the cursor. I know that
> > cursors are bad, but fail to see a set based solution for this situation. I
> > suspect that I am missing something obvious.
> > THanks for any information
> > Nick Lindner
> > "David Portas" wrote:
> >
> >> Glad to hear that. I don't really know Oracle well enough to say, it's
> >> just that the Oracle developers I've known seem to dwell a lot on
> >> cursor-based solutions and a lot of the traffic in Oracle forums seems
> >> to be the same. Questions from Oracle guys in this group are frequently
> >> of the form "how do I convert this Oracle cursor to SQL Server?".
> >> Probably that's not a representative selection of Oracle development
> >> practices, so I would be interested to hear the views of anyone with
> >> more knowledge of Oracle: What is best practice for when and when not
> >> to use a cursor in Oracle? It might help to know when answering this
> >> kind of question.
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >>
> >>
>
>
>