Query to delete record from two tables iin SQL Server

Last Reply 5 months ago By dharmendr

Posted 5 months ago

i have two table with similer column t_id in both table and i want to delete record from both tables base on date this is my query where i m wrong

DELETE t1, t2   FROM t1 INNER JOIN t2
WHERE t1.tid= t2.tid and t1.Start_Date between 2018-05-01 and 2018-05-10;

 

 

You are viewing reply posted by: dharmendr 5 months ago.
Posted 5 months ago

Hi ps222,

Refer the below query.

SQL

CREATE TABLE t1 (tid INT,Name CHAR(10),Start_Date DATETIME)
INSERT INTO t1 VALUES(1,'Name 1','2018-05-01')
INSERT INTO t1 VALUES(2,'Name 2','2018-05-03')
INSERT INTO t1 VALUES(3,'Name 3','2018-05-04')
INSERT INTO t1 VALUES(4,'Name 4','2018-05-12')
INSERT INTO t1 VALUES(5,'Name 5','2018-05-13')

CREATE TABLE t2 (tid INT,Age INT)
INSERT INTO t2 VALUES(1,15)
INSERT INTO t2 VALUES(2,12)
INSERT INTO t2 VALUES(3,13)
INSERT INTO t2 VALUES(4,14)
INSERT INTO t2 VALUES(5,18)

DELETE  FROM t2 WHERE t2.tid IN (SELECT tid FROM t1 WHERE t1.Start_Date BETWEEN '2018-05-01' AND '2018-05-10')
DELETE  FROM t1 WHERE t1.Start_Date BETWEEN '2018-05-01' AND '2018-05-10'

SELECT * FROM t1
SELECT * FROM t2

Output

t1

tid Name Start_Date
4 Name 4 2018-05-12 00:00:00.000
5 Name 5 2018-05-13 00:00:00.000

t2

tid Age
4 14
5 18