sql column data copying

I am trying to pull in some data from a sql table to another and getting this error when trying to process as follows:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

update myTable
set  DistributorTo_ID = (select DistributorTo_ID from StockAllocations where StockAllocations.ControlNo_ID= SIMs.ControlNo_ID)

Open in new window

Who is Participating?
Ephraim WangoyaConnect With a Mentor Commented:

pdate myTable
set  DistributorTo_ID = (select max(DistributorTo_ID)
                                      from StockAllocations
                                      where StockAllocations.ControlNo_ID= myTable.ControlNo_ID
                                      group by ControlNo_ID)
Ephraim WangoyaCommented:
what is the relationship between myTable and StockAllocations?
The query should of the form

update myTable
set  DistributorTo_ID = (select DistributorTo_ID
                                      from StockAllocations
                                      where StockAllocations.FoerignKey= myTable.PrimaryKey)
amillyardAuthor Commented:
the StockAllocations table has numerous transactions -- and some duplicated to the same Control_ID record ... what I am wanting to achieve is for sql to go through each record in StockAllocations table and update the myTable with the latest transaction.

there is a ControlNo_ID column in both tables (that links the numerous entries in StockAllocations)
amillyardAuthor Commented:
spot on - thank you :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.