Update statement works, but I don't think it should

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Update statement works, but I don't think it...

Update statement works, but I don't think it should

Rate Topic

Display Mode

Topic Options

Author

Message

computer.mike

Posted Tuesday, December 04, 2012 2:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:48 AM
Points: 33, Visits: 154

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

Post #1392709

Chris Harshman

Posted Tuesday, December 04, 2012 3:16 PM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:19 AM
Points: 1,563, Visits: 1,716

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.

Post #1392727

Michael Valentine Jones

Posted Tuesday, December 04, 2012 3:23 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 2,944, Visits: 10,508

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.

Post #1392730

Sean Lange

Posted Tuesday, December 04, 2012 3:23 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 8,605, Visits: 8,246

computer.mike (12/4/2012)

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1392731

Mark-101232

Posted Wednesday, December 05, 2012 2:05 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 1,500, Visits: 18,186

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.

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1392842

CELKO

Posted Wednesday, December 05, 2012 1:01 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

So I am updating one record [sic], ..

Rows are not records.

my UPDATE statement returns three records [sic].

No, an UPDATE changes the columns in rows; it returns nothing.

Normally I would expect to see an error. But my record [sic] is being updated. Is it picking the value with the lowest value? The first record [sic] in the set?

Yep! But it is worse than that. This old 1970's Sybase UPDATE.. FROM .. does not work. Hugo Cornelius and other MVPs, compiler writers, et al are urging that it be deprecated. Internally, this statement runs thru a list (not a set!) of matching rows in the physical order of the storage OR of one of the indexes, and it uses the last one it physically finds in a disk read. If an index is added, dropped or changed, or the table is re-organized, the UPDATE result can change. You will not have touched the UPDATE statement itself.

Today, we use the MERGE statement. It checks cardinality and will give you an error.

MERGE INTO Product_Totals AS PT
USING Price_List AS PL
ON PT.product_id = P.product_id
WHEN MATCHED
THEN UPDATE
SET revenue = price;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1393179

« Prev Topic | Next Topic »

Permissions