Update with Sub Query

I am trying to update the Org_SN field with the data from the [Serial Number] field based on the WHERE clause (MFG = N'GEM') AND (Model = N'TREK')) but I get

Msg 512, Level 16, State 1, Line 1
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    N
SET              Org_SN =
(
SELECT      [Serial Number]
FROM         AmcharNFA 
WHERE     (MFG = N'GEM') AND (Model = N'TREK'))
FROM AmcharNFA N

Open in new window

skull52Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
If you are intending to update this for all values, perhaps this is what your want...

UPDATE    N
SET              Org_SN =  [Serial Number]
FROM         AmcharNFA
WHERE     (MFG = N'GEM') AND (Model = N'TREK'))
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, and ...

If you highlight the below fragment of your statement, it is returning more than one row.

SELECT      [Serial Number]
FROM         AmcharNFA 
WHERE     (MFG = N'GEM') AND (Model = N'TREK')

Open in new window


Since you have it as a subquery setting a single value to the results of that subquery, then it has to return only one row / value.

To help, if you change the SELECT [Serial Number] to SELECT *, it'll flush out any differences that are causing more than one row.
0
 
skull52Author Commented:
Yes it returns more than 1 row I want it to copy all the serial numbers from the  [Serial Number] field to the Org_SN field based on the WHERE clause (MFG = N'GEM') AND (Model = N'TREK')) maybe I am using t he wrong approach
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you also want to read this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

now, here you want to update some column from other columns, but the same row?
or from other rows?

if it's from the same row: no need for a subquery: see jimhorn's syntax.
if it's from other rows: please explain how the find the correct row
if it's from several other rows , you need to do a SUM() or MAX(), for example)

please clarify
0
 
skull52Author Commented:
Jim,
That's exactly what I needed I should have got that, guess I was over thinking it.

Thanks
0
 
skull52Author Commented:
Angel,
Thanks for the links
0
All Courses

From novice to tech pro — start learning today.