[Last Call] Learn how to a build a cloud-first strategyRegister Now


sql column data copying

Posted on 2011-04-19
Medium Priority
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
  • 2
  • 2
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35429758
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

ID: 35429785
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

Ephraim Wangoya earned 2000 total points
ID: 35429795

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

ID: 35429830
spot on - thank you :-)

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

830 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