CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Amir
Amir

Reputation: 2022

SQL Server: Delete from table based on value of another table

I want to delete from t2 if same value of itemid,storeid,MSRTime does not exist on t1 and Same value of itemid,storeid,MSRTime exist on t3 and status is D. In below example i should be able to delete second row on t2 but not 1st row.

Table 1: t1

itemid |storeid|MSRTime
x        y        z

Table 2: t2

  itemid |storeid|MSRTime
    x        y        z
    a        b        c

Table 3: t3

 itemid |storeid|MSRTime|status
    x        y        z   D
    a        b        c   D

I tried doing this using join but i could not reach the desired result. Please help.
Thank You.

Upvotes: 0

Views: 173

Answers (4)

William Ledbetter
William Ledbetter

Reputation: 103

Not sure if this matches your environment, but programmatically it may be beneficial to limit the results you are comparing with the joins to only those that have a value of D in status. I would also try making a compound key using Coalese so that you are not having to match on three separate joins.

For example -

itemid |storeid|MSRTime|Key x y z xyz a b c abc

Upvotes: 0

PowerStar
PowerStar

Reputation: 895

I have created the whole script for your reference. Please use the last DELETE query for your scenario. That will do the trick. 

CREATE TABLE #T1 
(itemid VARCHAR(10)
,storeid VARCHAR(10)
,MSRTime VARCHAR(10))

INSERT INTO #T1 VALUES ('x','y','z')

SELECT * FROM #T1

GO

CREATE TABLE #T2 
(itemid VARCHAR(10)
,storeid VARCHAR(10)
,MSRTime VARCHAR(10))

INSERT INTO #T2 VALUES ('x','y','z'),('a','b','c')

SELECT * FROM #T2

GO

CREATE TABLE #T3 
(itemid VARCHAR(10)
,storeid VARCHAR(10)
,MSRTime VARCHAR(10)
,status VARCHAR(10))

INSERT INTO #T3 VALUES ('x','y','z','D'),('a','b','c','D')

SELECT * FROM #T3

GO

DELETE M 
FROM #T2 AS M INNER JOIN 
(SELECT itemid,storeid,MSRTime FROM
(SELECT itemid,storeid,MSRTime FROM #T3 WHERE status='D') T1
INTERSECT 
(SELECT itemid,storeid,MSRTime FROM
            (SELECT * FROM #T2
             EXCEPT
             SELECT * FROM #T1)  T2)) X
ON X.itemid = M.itemid AND X.storeid = M.storeid AND X.MSRTime = M.MSRTime           

GO

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

You can write the query almost exactly as you've described it:

declare @t1 table(itemid varchar(7),storeid varchar(9),MSRTime varchar(3))
insert into @t1(itemid,storeid,MSRTime) values
('x','y','z')
declare @t2 table(itemid varchar(7),storeid varchar(9),MSRTime varchar(3))
insert into @t2(itemid,storeid,MSRTime) values
('x','y','z'),
('a','b','c')
declare @t3 table(itemid varchar(7),storeid varchar(9),MSRTime varchar(3),status varchar(4))
insert into @t3(itemid,storeid,MSRTime,status) values
('x','y','z','D'),
('a','b','c','D')

delete from t2
from @t2 t2
    inner join
     @t3 t3
        on
            t2.itemid = t3.itemid and
            t2.storeid = t3.storeid and
            t2.MSRTime = t3.MSRTime and
            t3.status = 'D'
where
    not exists (
        select *
        from @t1 t1
        where t1.itemid = t2.itemid and
            t1.storeid = t2.storeid and
            t1.MSRTime = t2.MSRTime
    )

select * from @t2

Result:

itemid  storeid   MSRTime
------- --------- -------
x       y         z

Upvotes: 1

Veljko89
Veljko89

Reputation: 1953

Should be something like this 

-- delete t2
select * 
from table2 t2 
JOIN table3 t3 on t2.itemid = t3.itemid and
                  t2.storeid = t3.storeid and
                  t2.MSRTime = t3.MSRTime
LEFT JOIN table1 t1 on t2.itemid = t1.itemid and
                       t2.storeid = t1.storeid and
                       t2.MSRTime = t1.MSRTime
where t1.itemID IS NULL

Run the select first, if it gives you back right row, just un-comment delete and you are good to go

Upvotes: 0

Related Questions