I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server: How to modify data in any table in a resultset made up of joins

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

 

kick it on DotNetKicks.com

 

Print | posted on Sunday, August 19, 2007 1:26 AM | Filed Under [ SQL Server ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET