0

For the example, Table1 is defined as (id int, address varchar(100))

Will update every value in the table:

create table #test (id int, address varchar(100))

insert into #test (id, address)
values (5, 'test address')

update Table1
set
    address = (select top 1 address from #test)
where exists (select 1 from #test t where id = t.id)

Will only update the entry where id = 5:

create table #test (id2 int, address varchar(100))

insert into #test (id2, address)
values (5, 'test address')

update Table1
set
    address = (select top 1 address from #test)
where exists (select 1 from #test t where id = t.id2)

The only difference appears to be that one temp table has an ID that matches the source table and the other does not. Why would that matter? Or is there something more subtle, or more obvious, going on here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ryan
  • 3,127
  • 6
  • 32
  • 48
  • 8
    `select 1 from #test t where id = t.id` is not correlated to the outer query but in your second example `select 1 from #test t where id = t.id2` as the table `#test` no longer has an `id` column it is resolved from the outer scope. – Martin Smith Jul 12 '13 at 20:29
  • 3
    This is really off topic since it stems from just plain not understanding how to use `EXISTS`... – JNK Jul 12 '13 at 20:34
  • 3
    To make Martin Smith's comment a bit more explicit: in your first query, `where id = t.id` means `where t.id = t.id`, while in your second, it means `where Table1.id = t.id2`. – ruakh Jul 12 '13 at 20:36
  • 2
    @JNK, I don't see how that makes this off topic for Stack Overflow. Just because a poster doesn't understand something correctly doesn't mean it is off topic - very basic questions are still definitely on topic. – Joe Jul 12 '13 at 20:37
  • @Joe I disagree. Regardless, if you don't think it's off topic then surely it's a duplicate of a few dozen other questions? Basic syntax is normally not in-scope though. – JNK Jul 12 '13 at 20:39
  • 1
    SO is primarily syntax questions of various levels; basicness is not relevant. If it's a duplicate, then find the dup and link it, that of course is perfectly reasonable. – Joe Jul 12 '13 at 20:40
  • http://meta.stackexchange.com/questions/167732/are-basic-questions-cluttering-stack-overflow among many, many other meta-SO posts on the subject. – Joe Jul 12 '13 at 20:42
  • @Joe incredibly, I have a more than passing familiarity with what is and is not acceptable on this site and network. This question is based on a misunderstanding of how a basic SQL function works. Feel free to vote as you see fit but refrain from lecturing me on it, please. – JNK Jul 12 '13 at 20:45
  • Well, I feel like a real jackass. I had the second query first, in which the id value in the subquery is from Table1. I then wrote the first query to test an issue and saw the different results, still in the mindset that id was related to Table1, and not the temporary table. I appreciate the feedback. – Ryan Jul 12 '13 at 20:53
  • 2
    @Ryan As a rule, if you use `EXISTS` or `NOT EXISTS` you want to alias the outer reference and refer directly to the alias in the subquery. – JNK Jul 12 '13 at 20:54
  • 1
    @Ryan There is a similar (and more common gotcha) [with `IN` as well](http://stackoverflow.com/a/4594765/73226) – Martin Smith Jul 12 '13 at 21:40

1 Answers1

2

Clearly this always return true? 

select 1 from #test t where id = t.id

The stand alone id is using t of #test

In the second t does not have an ID so it looks up the tree to Table1

The message is don't use ambiguous names
If name are ambiguous then include the table (or alias) name

Why would you use exists that way?

paparazzo
  • 44,497
  • 23
  • 105
  • 176