sql column data copying

Posted on 2011-04-19
Last Modified: 2012-05-11
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

Question by:amillyard
    LVL 32

    Expert Comment

    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)

    Author Comment

    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)
    LVL 32

    Accepted Solution


    pdate myTable
    set  DistributorTo_ID = (select max(DistributorTo_ID)
                                          from StockAllocations
                                          where StockAllocations.ControlNo_ID= myTable.ControlNo_ID
                                          group by ControlNo_ID)

    Author Closing Comment

    spot on - thank you :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now