CODEWITHSUNDEEP
sql-server
Magnus
Magnus

Reputation: 18748

Update where exists, referencing the table being updated?

I need to update a table where one of the conditions is an EXISTS that needs to reference the table being updated.

I couldn't find a neat way to do this, so I resorted to this verbose SQL that gets the job done:

update TABLE_R set
   _deleted = 1
where id in (
   select id from TABLE_R r
   where (r.connectId = @theID)
   and (isnull(r._deleted, 0) = 0)
   and (r.type = 'Person')
   and exists(
      select id from TABLE_P p
      where (isnull(p.deleted, 0) = 1)
      and (isnull(p.social_security, '') <> '')
      and (p.social_security = r.social_security)
      and (p.trans_id = r.trans_id)
   )
)

Is there any way to simplify this? It seems like there should be a way to remove the outer nested select - but I couldn't find a way to reference TABLE_R in the EXISTS clause unless I wrap it in SELECT id FROM TABLE_R r.

Any ideas? Or is this as good as it gets?

I don't want to use JOIN, and I want to keep it as readable and simple as possible.

Upvotes: 0

Views: 85

Answers (2)

Chad Baldwin
Chad Baldwin

Reputation: 2602

Whould this work? Since this is an UPDATE, PLEASE make sure you run this on test data, and also run it as a select first to verify the results.

UPDATE r SET _deleted = 1
--SELECT *
FROM TABLE_R r
WHERE r.connectId = @theID
    AND ISNULL(r._deleted, 0) = 0
    AND r.[type] = 'Person'
    AND EXISTS (
        SELECT *
        FROM TABLE_P p
        WHERE p.deleted = 1
            AND p.social_security <> ''
            AND p.social_security = r.social_security
            AND s.trans_id = r.trans_id
    )

I removed a couple things.

(isnull(p.deleted, 0) = 1)

The ISNULL() is not needed here since you want p.deleted = 1, that will filter out NULL's anyway.

and (isnull(p.social_security, '') <> '')

I also removed this ISNULL() since p.social_security = r.social_security will eliminate the NULL's as well, but you still want to avoid joining on blank social_security's.

Upvotes: 2

pwilcox
pwilcox

Reputation: 5753

In Sql Server, you can take your logic out of the in clause. Outside of that, you can make it a bit more readable by avoiding parentheses when not necessary. The exists clause may seem verbose, but it's actually going to be the most performant way you can do it. But for readability I like to select 0 to make it clear we don't care about the results.

update  r
set     _deleted = 1
from    TABLE_R r
where   r.connectId = @theID
and     isnull(r._deleted, 0) = 0
and     r.type = 'Person'
and     exists(
            select  0
            from    TABLE_P p
            where   isnull(p.deleted, 0) = 1
            and     isnull(p.social_security, '') <> ''
            and     p.social_security = r.social_security
            and     s.trans_id = r.trans_id
        )

Upvotes: 1

Related Questions