CODEWITHSUNDEEP
sqlsql-server
writing
writing

Reputation: 25

Usage of "Not Exists" SQL statement

I have a table named DataTable which contains 5 columns: PK, Name, Type, Value, ParentID.

enter image description here

Now I need to merge the records whose ParentID = 1 into those whose ParentID = 2. So the records with ParentID = 1 are to be dissolved while ParentID = 2 means to be retained.

The merging rule is that if retained records have same Name and Type as dissolved, then just delete dissolved. otherwise update ParentID from 1 to 2

i.e. What I want achieve is something like

update DataTable set ParentID = 2 where ParentID = 1 and
(select Name, Type from DataTable where ParentID = 1)
Not Exists
(select Name, Type from DataTable where ParentID = 2)

The result after merge should be

enter image description here

Please help with the SQL Statement. Thanks.

Upvotes: 0

Views: 71

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46223

As noted in the comment by @GordonLinoff, this can also be accomplished with a MERGE statement.

DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t(
     PK int PRIMARY KEY
    ,Name varchar(10)
    ,Type varchar(10)
    ,Value varchar(10)
    ,ParentID int
    ,UNIQUE(Name, Type, PK)
);
INSERT INTO dbo.t VALUES
     (1,'str','string','abc',1)
    ,(2,'b','bool','Y',1)
    ,(3,'b','bool','N',2)
    ,(4,'str','enum','abc',2);
GO
MERGE dbo.t AS target
USING (
    SELECT
         Name
        ,Type
        ,Value
    FROM t
    WHERE t.parentid = 2) AS source ON
        source.Name = target.Name
        AND source.Type = target.Type
        AND target.ParentID = 1
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED BY SOURCE AND target.ParentID <> 2 THEN UPDATE SET ParentID = 2;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Hmmm . . . you seem to want to delete rows from the table where the parentid = 1 and a corresponding parentid = 2 exists. You are not 100% clear on what "corresponding" is, but something like this:

delete from t
    where t.parentid = 1 and
          exists (select 1
                  from t t2
                  where t2.parent_id = 2 and
                        t2.name = t1.name and
                        t2.type = t1.type
                 );

I see. This fixes getting rid of the rows. You can then do an update:

update t
    set parentid = 2
    where parentid = 1;

I should caution that this is subject to race conditions, if multiple threads are changing the table at the same time. I do think that merge also suffers from race conditions, so using merge wouldn't fix that problem.

Upvotes: 0

Related Questions