CODEWITHSUNDEEP
sql-servert-sql
LetUsSeng
LetUsSeng

Reputation: 27

SQL delete statement deletes instead of error out

Can someone explain how this query even executes? The column credit_transaction_key does not exist in financial_transaction, but it does exist in financial_transaction_jnl. What I don't get is why this query even executes if the column is in one table but not the other table. Shouldn't this just error out?

delete from financial_transaction_jnl
where credit_transaction_key in 
      (select credit_transaction_key
       from financial_transaction
       where account_key in 
             (select account_key
              from account
              where created_by = (select USER_KEY
                                  from USERS 
                                  where USER_ID = 'ME')
                and created_dttm > Cast('2/16/2017' as datetime)
             )
      )

financial_transaction: financial_transaction_key account_key

financial_transaction_jnl: financial_transaction_jnl_key financial_transaction_key credit_transaction_key

Upvotes: 0

Views: 115

Answers (2)

John Bollinger
John Bollinger

Reputation: 180968

The column credit_transaction_key does not exist in financial_transaction, but it does exist in financial_transaction_jnl. [...] Shouldn't this just error out?

No. If indeed financial_transaction does not contain a column named credit_transaction_key then in the subquery that identifier will be resolved against the column list of the outer row set (i.e. the columns of financial_transaction_jnl) for the current row. Since the value selected is therefore the same value that is being compared against the selection, the query you present has the same effect as this slightly simpler one:

delete from financial_transaction_jnl
where exists (
  SELECT 1
  from financial_transaction
  where account_key in (
    select account_key
    from account
    where created_by = (SELECT USER_KEY  FROM USERS WHERE USER_ID = 'ME')
      AND created_dttm > Cast('2/16/2017' AS DATETIME)
  )
)

Inasmuch as the WHERE clause of the outer query therefore appears not to depend on the values in its target table, either all rows or none will be deleted. That seems unlikely to be the desired effect.

Qualifying the column name in the outer query, as another answer suggests, will not alter this meaning. Qualifying the column name in the inner query as financial_transaction.credit_transaction_key would indeed cause the query to error out.

Upvotes: 1

EoinS
EoinS

Reputation: 5482

You need to use table aliases.

SQL has to assume where your columns are coming from.

in this case, everything gets deleted:

financial_transaction.redit_transaction_key in
(SELECT credit_transaction_key from  financial_transaction

but you are probably trying to do this:

financial_transaction_jnl.redit_transaction_key in
    (SELECT credit_transaction_key from  financial_transaction

Upvotes: 1

Related Questions