CODEWITHSUNDEEP
sqlsql-servert-sqlsql-updatesubquery
EllieK
EllieK

Reputation: 273

Update Statement Retrieves Incorrect Values

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?

Upvotes: 2

Views: 81

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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.

Upvotes: 2

Related Questions