Update statement works, but I don't think it should
-
So I am updateing one record, my update statement returns three records. Normally I would expect to see an error. But my record is being updated. Is it picking the value with the lowest value? The first record in the set?
I didn't think this was possible. Anyone know anything about how SQL Server decides what value to update a field with when more than one record are returned by the update statement?
example:
DECLARE @ProductTotals TABLE (ProductID varchar(10), Revenue money)
insert into @ProductTotals (ProductID, Revenue)
values ('Bike',null)
select * from @ProductTotals
DECLARE @Price TABLE ( ProductID varchar(10), Price money)
insert into @Price
values('Bike',10),
('Bike',15),
('Bike',20)
select * from @Price
update @ProductTotals
set Revenue = Price
from @ProductTotals PT join @Price P on PT.ProductID = P.ProductID
select * from @ProductTotals
-
Are you saying that you are updating 1 record using the results of a subquery which is returning 3 records? In that case it really isn't definded which of the 3 values would be the final one. It would be better to ensure your subquery only returned 1 row per row you were updating.
-
An UPDATE updates all rows that match the selection criteria. UPDATE statements do not return rows, unless you have an OUTPUT clause.
If you really want some help, you need to post your UPDATE statement, a statement to create the table that will be updated, and some inserts to populate that table with data to be updated.
-
computer.mike (12/4/2012)
So I am updateing one record, my update statement returns three records. Normally I would expect to see an error. But my record is being updated. Is it picking the value with the lowest value? The first record in the set?I didn't think this was possible. Anyone know anything about how SQL Server decides what value to update a field with when more than one record are returned by the update statement?
Something like:
Update MyTable
set MyColumn = SomeValue
from MyTable mt
join MyOtherTable mot on mt.key = mot.key
where SomeColumn = SomeCondition
And if you run that as a select it would return 3 rows?
The actual value will be the value from the last row in the result set. Of course there is no order by clause so there is no way to know what the order is. If you are uncertain which row why are you running the update like this? You need to further your where clause to get a single row.
--edit--
To further clarify, what Michael said above is totally accurate. In the scenario I posted it would update all 3 rows and the value would come from the "last" one, what ever that might be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This behaviour is documented in BOL, look at the section 'Using the UPDATE statement with information from another table'
here
http://msdn.microsoft.com/en-us/library/ms177523(v=sql.100).aspx
You need to correct your UPDATE statement to join to only one row. Alternatively using the MERGE statement would generate an error if multiple rows are updated.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply