Mladen Prajdić Blog

Recently i'm seeing a lot of questions about how to modify data in the tables which are part of the multi-join select statement.

And i keep wondering how can this be so hard to remember. The methodology is quite simple.

Let's split the SELECT statement in 2 parts:

-- part 1:
SELECT  *
– part 2:
FROM    t1
JOIN t2 ON t1.id = t2.t1Id
JOIN t3 ON t1.id = t3.t1Id
LEFT JOIN t4 ON t3.id = t4.t3Id
WHERE   t3.id = 2

Part 1 tells us what kind of statement will we use.

Part 2 tells us from where the data will come from and it never changes in other DML staments!

For example let's say that you want to delete data from table t2 but only those rows which are returned in the upper resultset:

-- part 1:
DELETE T2    -- here you can say also DELETE T4, others will return Foreign Key error
-- part 2:
FROM    t1
        JOIN t2 ON t1.id = t2.t1Id
        JOIN t3 ON t1.id = t3.t1Id
        LEFT JOIN t4 ON t3.id = t4.t3Id
WHERE   t3.id = 2

We can see that the part 2 hasn't changed at all. Same thing applies to Update and Insert.

It's simple isn't it?

Full code to demostrate the principle: 

CREATE TABLE t1 (id INT IDENTITY(1,1) PRIMARY KEY, t1_text VARCHAR(100))
GO
CREATE TABLE t2 (id INT IDENTITY(1,1) PRIMARY KEY, t1Id INT REFERENCES t1(id), t2_text VARCHAR(100))
GO
CREATE TABLE t3 (id INT IDENTITY(1,1) PRIMARY KEY, t1Id INT REFERENCES t1(id), t3_text VARCHAR(100))
GO
CREATE TABLE t4 (id INT IDENTITY(1,1) PRIMARY KEY, t3Id INT REFERENCES t3(id), t4_text VARCHAR(100))
GO
– some sample data
INSERT INTO t1 (t1_text)
SELECT 't1 name 1' UNION ALL
SELECT 't1 name 2' UNION ALL
SELECT 't1 name 3' UNION ALL
SELECT 't1 name 4'
INSERT INTO t2 (t1Id, t2_text)
SELECT 1, 't2 name 1' UNION ALL
SELECT 1, 't2 name 2' UNION ALL
SELECT 1, 't2 name 3' UNION ALL
SELECT 2, 't2 name 4' UNION ALL
SELECT 2, 't2 name 5' UNION ALL
SELECT 3, 't2 name 6' UNION ALL
SELECT 4, 't2 name 7' UNION ALL
SELECT 4, 't2 name 8' UNION ALL
SELECT 4, 't2 name 9' UNION ALL
SELECT 4, 't2 name 10'
INSERT INTO t3 (t1Id, t3_text)
SELECT 1, 't3 name 1' UNION ALL
SELECT 2, 't3 name 2' UNION ALL
SELECT 3, 't3 name 3' UNION ALL
SELECT 4, 't3 name 4' UNION ALL
SELECT 4, 't3 name 5'
INSERT INTO t4 (t3Id, t4_text)
SELECT 1, 't4 name 1' UNION ALL
SELECT 1, 't4 name 2' UNION ALL
SELECT 1, 't4 name 3' UNION ALL
SELECT 3, 't4 name 4' UNION ALL
SELECT 3, 't4 name 5' UNION ALL
SELECT 4, 't4 name 6'
GO
– simple select 
SELECT  *
FROM    t1
JOIN t2 ON t1.id = t2.t1Id
JOIN t3 ON t1.id = t3.t1Id
LEFT JOIN t4 ON t3.id = t4.t3Id
GO
– demonstrate with delete
DELETE  t4
FROM    t1
JOIN t2 ON t1.id = t2.t1Id
JOIN t3 ON t1.id = t3.t1Id
LEFT JOIN t4 ON t3.id = t4.t3Id
WHERE   t4.id = 1
– lets see what we got
SELECT * FROM t4
GO
– demonstrate with update
UPDATE  t3
SET     t3_text = 'we just updated the t3 table'
FROM    t1
JOIN t2 ON t1.id = t2.t1Id
JOIN t3 ON t1.id = t3.t1Id
LEFT JOIN t4 ON t3.id = t4.t3Id
WHERE   t4.id = 2
– lets see what we got
SELECT * FROM t3
GO
– demonstrate with insert
CREATE TABLE #tempResults(id int, ref_id INT, temp_text VARCHAR(100))
INSERT INTO #tempResults(id, ref_id, temp_text)
SELECT  t4.id, t4.t3Id, t4_text
FROM    t1
JOIN t2 ON t1.id = t2.t1Id
JOIN t3 ON t1.id = t3.t1Id
LEFT JOIN t4 ON t3.id = t4.t3Id
WHERE   t4.id = 3
– lets see what we got
SELECT * FROM #tempResults
– clean up
DROP TABLE #tempResults
DROP TABLE t4
DROP TABLE t3
DROP TABLE t2
DROP TABLE t1