Re: Using Cursors
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/01/05
- Next message: Hugo Kornelis: "Re: Using Cursors"
- Previous message: Werner: "Re: Query question"
- In reply to: Nickl: "Re: Using Cursors"
- Next in thread: Nickl: "Re: Using Cursors"
- Reply: Nickl: "Re: Using Cursors"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 1 Feb 2005 09:16:01 +0100
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 >> -- >> >>
- Next message: Hugo Kornelis: "Re: Using Cursors"
- Previous message: Werner: "Re: Query question"
- In reply to: Nickl: "Re: Using Cursors"
- Next in thread: Nickl: "Re: Using Cursors"
- Reply: Nickl: "Re: Using Cursors"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
