May 23, 2006 at 5:52 am
im trying to update rows in a table as shown below, there are many many rows i need to update.
update
newReport1
set
customerName = (select c.customername from customer c inner join newReport1 nr on c.customerID = nr.customerID
where c.customerId = nr.customerID)
i keep getting this error
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.
May 23, 2006 at 6:11 am
got it sorted. just blind looking at code. was easy. sorry to bother you all
May 23, 2006 at 6:12 am
First of all, why are you repeating your join criteria? The ON criteria is doing exactly the same job as the WHERE criteria inside your subquery.
However, you want to do this a bit differently. Run that subquery and see what you get. You will see pretty much all of your customers, I'm willing to bet. You aren't actually restricting them, except by whether or not a record exists for them in the newReport1 table.
Try:
UPDATE newReport1
SET customerName = (SELECT c.customerName FROM customer c WHERE c.customerID = newReport1.customerID)
As a final note, if this is actually the query you are trying to run, it looks like you have a problem with denormalization. I would highly recommend joining the newReport1 and customer tables when producing your results, rather than storing the duplicate data.
May 23, 2006 at 6:44 am
I also suggest an additional condition to avoid updating any customer names that are already correct, on the basis that reads are way faster than writes:
update nr1
set CustomerName = c.CustomerName
from NewReport1 nr1
join Customer c on nr1.CustomerID = c.CustomerID
where nr1.CustomerName <> c.CustomerName or nr1.CustomerName is null
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply