asked on

Update colums in one table from values in another table

Hi,

I would like to update a column in one table (Target) from the contents of on column in another table 9Source).

The value in the source table is unique, but needs to be added to the target table multiple times. Something like this:

UPDATE a
SET a.[StockNumber] = (SELECT h.[StockNumber]
                                          FROM Stock h
                                          JOIN Sales a
                                          ON a.OrderNumber = h.OrderNumber)
FROM [Stock] a

So OrderNumber appears multiple times in the Stock table and needs to be updated with the StockNumber.

When I run the above, I get the following: query returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help would be appreciated.

Thanks

Vitor Montalvão

So you want to update the StockNumber from Stock table with the same field from the same table?
Can you explain why do you want to do that? As I understand it will update the field with the same value so what's the interest here?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Morpheus7

ASKER

Thanks Jim, that is just what I need. I take note of you point about aliases. Thanks again.