Update with Sub-Query Result - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update with Sub-Query Result

Author

Topic

HappyCamper
Starting Member

8 Posts

Posted - 2005-09-21 : 13:55:10

I'm attempting to avoid using a cursor for updating multiple records.

The task is to:
a) insert a record in table A and get the @@identity
b) update another record in table B with the @@identity
c) do this multiple times without a cursor

My question: is there a way to execute a stored procedure as a sub-query and use the result to update another record? Below is a sample of what I attempted, but does not work.

DECLARE @tblPerson TABLE(LName varchar(20), fkPhone int NULL)
DECLARE @tblPhone TABLE(pKey int identity, Phone varchar(20))

-- Add Smith to the person table, without phone
INSERT INTO @tblPerson SELECT 'Smith', null

-- Assumes a sproc exists called 'AddPhone' which returns the @@Identity value
-- Insert a new phone and set the fkPhone to @@Identity returned by sproc
UPDATE @tblPerson
SET fkPhone = (EXEC AddPhone '555-1234') -- syntax error
WHERE LName = 'Smith'

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-21 : 14:02:20

Hopefully you don't actually have a Phone table.

Have AddPhone output the identity value.

DECLARE @i int

EXEC AddPhone '555-1234', @i OUTPUT

UPDATE @tblPerson
SET fkPhone = @i
WHERE LName = 'Smith'

Also use SCOPE_IDENTITY() instead of @@IDENTITY.

Tara

HappyCamper
Starting Member

8 Posts

Posted - 2005-09-21 : 14:28:48

Thanks Tara - no, I don't have a phone table.

This was a simple illustration for a (real life) complex problem. I understand your reply, but I'd still need a cursor for updating multiple records:

DECLARE @curLName varchar(20), @i int
DECLARE myCursor CURSOR FOR SELECT LName FROM @tblPerson
OPEN myCursor
FETCH NEXT FROM myCursor INTO @curLName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC AddPhone 'SomeNumberFromList', @i OUTPUT
UPDATE @tblPerson
SET fkPhone = @i
WHERE LName = @curLName
FETCH NEXT FROM myCursor INTO @LName
END
CLOSE myCursor
DEALLOCATE myCursor

All this aside, is there a way to execute a sproc within an Update statement?

UPDATE @tblPerson
SET fkPhone = (EXEC AddPhone '555-1234') <-- can this be done?
WHERE LName = 'Smith'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-21 : 14:33:57

quote:

All this aside, is there a way to execute a sproc within an Update statement?

No.

Where is the data coming from? Your application, other tables? Having additional information about your problem will help us come up with a non-cursor solution.

Tara

HappyCamper
Starting Member

8 Posts

Posted - 2005-09-21 : 16:00:33

The underlying problem I'm having is a poorly designed database which I have no responsiblity for or control over, but unfortunately still have to work with.

I need to update a table's set of records with some known values, plus one unknown value - the unknown value comes back when an insert is made into a second table (by executing a stored procedure) - the second table's identity key becomes a foriegn key to the table I'm updating.

I can do this with a cursor... looping through each record that I need to update, exec the sp in the second table, and using the identity returned, update the current record in the cursor.

Since cursors are very slow and have a lot of overhead, I was hoping to be able to update my records in one pass by using the "exec" in my update statement, thus avoiding the cursor.

My understanding is that sub-queries execute on an individual basis, once for each record within a rowset. I thought I'd be able to execute the sp as a sub-query in the Update statement and use the returned result to set the unknown value I need. SQL doesn't seem to like that idea as much as I do.

BTW, I'd post the code I have, however it's very lengthy and confidential.

Thanks for you help thus far! I really do appreciate it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-21 : 16:20:43

To get around the cursor, you'd have to use a variation of your stored procedure. You'd take the code out of that stored procedure, modify it so that it can handle more than one row (using joins), then use that query to update your data. Without seeing DDL, sample data, expected result set, and the stored procedures, it's hard for us to provide the exact details on what you need. But I would bet that this can be handled without a cursor. The DDL and sample data don't even need to be real, they just need to be able to demonstrate your problem.

Tara

HappyCamper
Starting Member

8 Posts

Posted - 2005-09-23 : 11:53:53

Here's the scenerio that hopefully will help clarify what I'm trying to do.

We have a web-based application that manages customer accounts. The application allows the administrator to disable one or more customer records in a single action, using the same comment for all records (i.e. "Non payment").

When doing so, there are two parameters that need to be submitted:

1. A flag to indicate the customer is disabled.
2. The reason for disabling the account.

The database is set up with 2 tables:

1. A "Customer" table
2. A "Notes" table

For simplicity sake, here's a brief description of the relationship between the customer table and the notes table.

1. Customer (CustomerID int, Disabled bit, NoteID int NULL) -- NoteID is a nullable fKey to Notes table
2. Notes (NoteID int identity, Reason varchar(200))

The way the database is designed, a Note record may not exist for any given customer until a note is entered (which is why the NoteID in Customers is nullable). Once entered, the NoteID from the Notes table is used as a Foreign Key in the Customer table.

As mentioned above, the administrator can disable multiple accounts at once. In my scenerio, we can assume that all the customers being updated do NOT already have an associated Note record. The challange is that I must first create a Note record, get the NoteID (@@Identity), and then update the customer table's NoteID (fKey) with that value. I can do this with a cursor (as shown in a previous post). I'm attempting to do this without a cursor. Here's what I had hoped I could do, but I get a parse error.

"InsertNote" stored procedure inserts a new Notes record and returns the @@Identity

-- Disable multiple customers with reason
UPDATE Customer
SET Disabled = 1 -- flag customer is disabled
, NoteID = (EXEC InsertNote 'Non-Payment') -- Insert Note record and set NoteID fKey
WHERE CustomerID IN (1,2,33,45,123)

Hope this helps explain my situation. Again, thanks so much!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-23 : 12:05:53

It seems to me that the application should call the stored procedure, or whatever that does the update and insert, once per customer that needs to be disabled.

How is the data being passed to you? Is it comma separated?

Tara

HappyCamper
Starting Member

8 Posts

Posted - 2005-09-23 : 12:39:55

Yes, the data is comma seperated. I have a UDF that parses the comma-delimited values and returns a table populated with those values. I use this table to do a join to the customer records that need updating. This works well.

Your idea of the application calling the sproc multiple times would work, although I'm not sure this is the best approach - this is similar to using a cursor, only the work would be placed on the application side.

After a lot of research and experimenting, it appears there is no way within sql to do what I'm attempting (without a cursor). The cursor works fine, but takes an extremely long time to execute when a large amount of customers are updated at once. I'm now looking at how to speed up the transactions that are taking place within the cursor. My ultimate goal is take make this procedure execute faster.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources