I received the following message from a DTS package that fails. 'Subquery
returned more than 1 value. This is not permitted when the subquery follows =,
!=, <, <=, >,>= or when the subquery is used as an expression'. It occurs
during an update with the following statement:

'UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT Provider.Provider_Key FROM dbo.Provider WHERE
Provider.Datasource_ID = FEIProviderStatus.DatasourceID AND Provider.
Provider_key <> 8602)

UPDATE dbo.FEIProviderStatus
SET PayorKey = (SELECT Payor.Payor_Key FROM dbo.Payor WHERE Payor.
Datasource_ID = FEIProviderStatus.DatasourceID)'

Thanks in advance.

Jay

--
Message posted via http://www.sqlmonster.com

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Jay,

It is telling you that your subselects are getting more than 1 row back.
So, a couple of possibilities are:

1. The join and filter on the subselect are not as specific as they should
be. That may mean investigating the data to determine what is being
overlooked.

2. Yes, multiple rows are being returned in some instances, but it does not
matter to you since you just want a single Provider_Key. (Perhaps you even
know that the multiple Provider_Key values are identical.) In that case,
use an aggregate to enforce returning a single row. For example:

UPDATE dbo.FEIProviderStatus
SET ProviderKey = (SELECT MAX(Provider.Provider_Key) -- MAX enforces a
single value
FROM dbo.Provider
WHERE Provider.Datasource_ID = FEIProviderStatus.DatasourceID
AND Provider.Provider_key <> 8602)

RLF

"Jay via SQLMonster.com" <u7124@uwe> wrote

Russell, thanks much for this help. It worked.

Jay,

Russell Fields wrote:
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200802/1