Using Subqueries in an UPDATE Statement

[Rachel]

hi,

I have the following stored procedure and i would like to know how I
would update a table with the results:

Create PROCEDURE Proc_UpdateBalance

AS
RETURN
select
i.Case_ID,
count(*) as InvoiceCount,
sum(i.Amount) as invoiceAmount,
count(distinct i.Case_ID) as CaseCount

from tbl_Invoice i
inner join
tbl_Case c on i.Case_ID = c.Case_ID
group by i.Case_ID

This returns:

Case_ID InvoiceCount InvoiceAmount CaseCount
121212 2 £9000.00 1
121213 1 £1000
1
121214 1 £8000
1

I want to insert InvoiceAmount into a table called tbl_Case (field
named Balance). How do I do this?

Many Thanks

Rachel

 

[Sylvain Lafontaine]

It's complicated because a stored procedure (SP) return a (zero, one or
more) resultsets and not a table or a view. Resultsets are special objects
designed to be returned to the client and cannot be used directly in a
Select or Update statement.

If you want to use the result of a SP, the easiest way would be to recode as
either a View or an User Defined Function (UDF); otherwise, you will have to
create a linked server and call the SP using the OPENROWSET, OPENQUERY or
OPENDATASOURCE methods or the use the INSERT-EXEC syntaxe to put the result
into a temporary table or use a temporary table to etablish a communication
link between the local code and the SP. For some examples, see:

http://www.sommarskog.se/share_data.html

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


hi,

I have the following stored procedure and i would like to know how I
would update a table with the results:

Create PROCEDURE Proc_UpdateBalance

AS
RETURN
select
i.Case_ID,
count(*) as InvoiceCount,
sum(i.Amount) as invoiceAmount,
count(distinct i.Case_ID) as CaseCount

from tbl_Invoice i
inner join
tbl_Case c on i.Case_ID = c.Case_ID
group by i.Case_ID

This returns:

Case_ID InvoiceCount InvoiceAmount CaseCount
121212 2 £9000.00 1
121213 1 £1000
1
121214 1 £8000
1

I want to insert InvoiceAmount into a table called tbl_Case (field
named Balance). How do I do this?

Many Thanks

Rachel

 

[Rachel]

It's complicated because a stored procedure (SP) return a (zero, one or
more) resultsets and not a table or a view.  Resultsets are special objects
designed to be returned to the client and cannot be used directly in a
Select or Update statement.

If you want to use the result of a SP, the easiest way would be to recode as
either a View or an User Defined Function (UDF); otherwise, you will have to
create a linked server and call the SP using the OPENROWSET, OPENQUERY or
OPENDATASOURCE methods or the use the INSERT-EXEC syntaxe to put the result
into a temporary table or use a temporary table to etablish a communication
link between the local code and the SP.  For some examples, see:

http://www.sommarskog.se/share_data.html

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


hi,

I have the following stored procedure and i would like to know how I
would update a table with the results:

Create PROCEDURE Proc_UpdateBalance

AS
RETURN
select
i.Case_ID,
count(*) as InvoiceCount,
sum(i.Amount) as invoiceAmount,
count(distinct i.Case_ID) as CaseCount

from   tbl_Invoice i
inner join
tbl_Case c on i.Case_ID = c.Case_ID
group by i.Case_ID

This returns:

Case_ID          InvoiceCount        InvoiceAmount         CaseCount
121212            2                        £9000.00                 1
121213            1                        £1000
1
121214            1                        £8000
1

I want to insert InvoiceAmount into a table called tbl_Case (field
named Balance). How do I do this?

Many Thanks

Rachel

Many Thanks, I have created a view of it instead.