Very Computer

I tried doing this in a stored procedure based on a View
with an inner join between the 2 tables as follows:

Update View1
Set CusCreditAmt = CreditAmt

I received an error that the subquery returned more than
one value.  Is there anyway to do something like this and
make it work?

Try this query. It is using ANSI compatible syntax:

UPDATE CusOrder
SET CusCreditAmt = (
    SELECT CreditAmt
    FROM CusCredits
    WHERE CusOrderID = CusOrder.CusOrderID
)
WHERE EXISTS (
    SELECT *
    FROM CusCredits
    WHERE CusOrderID = CusOrder.CusOrderID
)

or this (T-SQL proprietary):
UPDATE CusOrder
SET CusCreditAmt = CreditAmt
FROM CusOrder
    JOIN CusCredits
    ON CusOrder.CusOrderID = CusCredits.CusOrderID

--
Ivan Arjentinski
----------------------------------------------------------------------
Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
scripts), sample data (INSERT INTO....) and desired result set when asking
for assistance is highly appreciated
----------------------------------------------------------------------

Quote:> How can I do an update on multiple records.  I have fields
> CusOrderID and CusCreditAmt in table CusOrder and fields
> CusOrderID and CreditAmt in table CusCredits.  I would
> like to update the value in the CusCreditAmt field in
> CusOrder to equal the CreditAmt field in CusCredits where
> CusOrderID is the same in both tables.

> I tried doing this in a stored procedure based on a View
> with an inner join between the 2 tables as follows:

> Update View1
> Set CusCreditAmt = CreditAmt

> I received an error that the subquery returned more than
> one value.  Is there anyway to do something like this and
> make it work?