2

I have the following two tables

PARTNERS

CREATE TABLE [dbo].[Partners](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[PartnerID] [varchar](50) NOT NULL,
[PartnerName] [nvarchar](200) NOT NULL,
[Active] [bit] NOT NULL,
[InactiveDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[RealmID] [int] NOT NULL

REALMS

CREATE TABLE [dbo].[Realms](
[RealmID] [int] NOT NULL,
[RapRealmID] [varchar](50) NOT NULL,
[RealmName] [varchar](50) NOT NULL,
[Description] [varchar](200) NULL,
[ApplicationID] [int] NOT NULL

When I run the following, correctly constructed, update statement everything works as expected --

UPDATE dbo.Partners 
SET RealmID = (Select RealmID From dbo.Realms WHERE RapRealmID = 'MyCompany')

But if I incorrectly request the non-existent column ID and not the RealmID from the Realms table, like so --

UPDATE dbo.Partners 
SET RealmID = (Select ID From dbo.Realms WHERE RapRealmID = 'MyCompany')

no error is generated and the Partners table is updated by selecting the ID from the Partners table for that record (i.e. The Partners' table ID is just moved to the RealmID field).

I know using aliases in the subquery will overcome this issue. But why would SQL Server not evaluate the Select in the subquery as an atomic unit that must successfully execute? Running Select ID From dbo.Realms WHERE RapRealmID = 'MyCompany' on its own, fails. Why does it succeed when it's a subquery?

EllieK
  • 259
  • 4
  • 14

1 Answers1

2

It is all about scope. ID refers to outer dbo.Partners table:

UPDATE dbo.Partners SET RealmID = (Select ID 
                                   From dbo.Realms 
                                   WHERE RapRealmID = 'MyCompany');
<=>
UPDATE dbo.Partners SET RealmID = (Select dbo.Partners.ID 
                                   From dbo.Realms 
                                   WHERE RapRealmID = 'MyCompany');

Standalone query will error:

Select ID From dbo.Realms WHERE RapRealmID = 'MyCompany';
-- there is no ID column

From Subqueries:

The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level. If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • But don't my parenthesis change the scope for the subquery? How can the parenthesized statement expand its scope to the containing query? I think that's what has me confused? – EllieK Jun 20 '18 at 19:05
  • 1
    Paranthesis do not change relation outer-inner query. – Lukasz Szozda Jun 20 '18 at 19:13