1

this illustrates the issue:

CREATE TABLE Table_A (id int, value char)
INSERT INTO Table_A VALUES (1, 'A')
INSERT INTO Table_A VALUES (2, 'B')
CREATE TABLE Table_B (id int, value char)
INSERT INTO Table_B VALUES (1, 'C')
INSERT INTO Table_B VALUES (1, 'D')

If you run

UPDATE a SET a.value = (SELECT b.value FROM Table_B b WHERE a.id = b.id)
FROM Table_A a, Table_B b WHERE a.id = b.id

You get an error saying

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

But if you run this

UPDATE a SET a.value = b.value
FROM Table_A a, Table_B b WHERE a.id = b.id

No error is thrown and you get the row updated, why is that?

Edit:

Sorry guys, you seem to focusing on explaining why the first query gives error, but I think that is obvious and to me that is a desire result (because setting value of Table_A for id 1 to value of Table_B with id 1 is undefined when there are multiple values in Table_B with id 1)

My question is actually asking why the second query does not give you an error, which is causing trouble to me (i.e. I want it to break if I have more than one row with the same id but different values)

user1589188
  • 5,316
  • 17
  • 67
  • 130
  • So what are the expected results at the end? You want the row with id =1 to end up with C or D? – Martin Smith Aug 07 '14 at 05:52
  • I expect error. Currently using the second query (which I believe is the normal way to write update), it is causing me trouble. I can change my SQL to the first query, but I believe it is so weird to do so. – user1589188 Aug 07 '14 at 06:03
  • What do you mean by "normal"? The second one is proprietary TSQL syntax. The first query or `merge` both give your desired semantics and are standard SQL. – Martin Smith Aug 07 '14 at 06:26
  • I mean given a task to update a.value using b.value on same id, you normally just write a.value = b.value, instead of using sub-query a.value = (SELECT b.value...) just for the sake of breaking on multiple rows with same id. Which is why I ask why the 'normal', 'clean', 'direct' way of writing a.value=b.value does not throw an error? It just picks a random value from the rows and complete the update. – user1589188 Aug 07 '14 at 06:35
  • Well you'd normally be doing it wrong then if you don't want those semantics. As to why those semantics were chosen who knows? Unlikely to change now though for backwards compatibility. Maybe same designer that decided to allow implicit casts from int to date time! – Martin Smith Aug 07 '14 at 06:58
  • @MartinSmith So I take it you are saying we should use sub query instead of a join to do update because only sub query can break on multiple assignment? – user1589188 Aug 07 '14 at 23:08
  • 1
    Use the query that gives you the semantics and execution plan that you want. `merge` will also raise an error if the source is many rows to the target's one. – Martin Smith Aug 07 '14 at 23:12

3 Answers3

2

You got that error because you are using subquery when you set a new value and the subquery return more than 1 result.

SET a.value = (SELECT b.value FROM Table_B b WHERE a.id = b.id)

It will error when update a value with id = 1, because there is two record that have id = 1 in table b.

So your query will look like this (this is only for illustration and of course will cause an error)

UPDATE a SET a.value = ('C', 'D') 
FROM Table_A a, Table_B b WHERE a.id = b.id

When you're using this query

UPDATE a SET a.value = b.value
FROM Table_A a, Table_B b WHERE a.id = b.id

You are join the table a with table b using id field, so the result is

a.id => a.value => b.value : 1 A C

a.id => a.value => b.value : 1 A D

No entry record for id = 2 because there is no matching record in table b.

So your update query will looks like this

UPDATE a SET a.value = 'C'
FROM Table_A a, Table_B b WHERE a.id = 1


UPDATE a SET a.value = 'D'
FROM Table_A a, Table_B b WHERE a.id = 1
Iswanto San
  • 18,263
  • 13
  • 58
  • 79
  • Thanks for your edit. So you are saying it did perform two updates in the second query? But the result still says `(1 row(s) affected)` – user1589188 Aug 07 '14 at 05:56
  • And if what you said is true, Table_A will have value 'D', but test the query, you will find 'C' instead. – user1589188 Aug 07 '14 at 06:05
1

Because your subquery will return both more than 1 result. The Assign statement will not accept more than 1 value.

You have to use JOIN

May be something like this

UPDATE A
SET A.value = B.value
FROM Table_A A INNER JOIN Table_B  B ON A.id = B.id

FIDDLE DEMO

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • Thanks for your effort but my second query is effectively the same as yours. Please try to explain why the second query does not give out an error. – user1589188 Aug 07 '14 at 06:00
0

The reason your second query does not error our is because it will assign the first available B.value to a.value where it satisfies the condition A.id = B.id

Try the following snippet and you will see how the Update...Set works. 

create table #temp (id int, name varchar(100), phone int)
create table #temp1 (id int, phone int)
insert into #temp (id, name)
select 1, 'Mark' union
select 2, 'JOhn' union
select 3, 'Jerry'

insert into #temp1 (id, phone)
select 1, 123456 union
select 1, 222564 union
select 1, 222210

select * from #temp
select * from #temp1

update t
set phone = t1.phone
from #temp1 t1, #temp t
where t.id = t1.id

select * from #temp
Sanket J
  • 187
  • 1
  • 1
  • 12
  • Thanks. But I fail to see anything extra in your answer then my example. If you think the update will assign the first available b.value ONLY (as oppose to @Iswanto San where 2 updates were performed), then I should ask what happen to the second one? – user1589188 Aug 07 '14 at 23:04
  • Since, the Set can assign only value at a time, I am not sure if 2 updates will be performed (as @Iswanto San suggested). As you mentioned that you got the message of `(1 row(s) affected)`, that is pretty evident that only one update is performed with the 1st available value when the where clause finds a match. – Sanket J Aug 11 '14 at 18:04