<--- --------------------------------------------------------------------------------------- ----
Blog Entry:
Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!)
Author:
Ben Nadel / Kinky Solutions
Link:
http://www.bennadel.com/index.cfm?dax=blog:938.view
Date Posted:
Sep 4, 2007 at 10:31 AM
---- --------------------------------------------------------------------------------------- --->
DECLARE
@boy TABLE
(
id INT,
name VARCHAR( 30 ),
is_stud TINYINT
)
;
DECLARE
@girl TABLE
(
id INT,
name VARCHAR( 30 )
)
;
DECLARE
@relationship TABLE
(
boy_id INT,
girl_id INT,
date_started DATETIME,
date_ended DATETIME
)
;
INSERT INTO @boy
(
id,
name,
is_stud
)(
SELECT 1, 'Ben', 0 UNION ALL
SELECT 2, 'Arnold', 0 UNION ALL
SELECT 3, 'Vincent', 0
);
INSERT INTO @girl
(
id,
name
)(
SELECT 1, 'Maria Bello' UNION ALL
SELECT 2, 'Christina Cox' UNION ALL
SELECT 3, 'Winona Ryder'
);
INSERT INTO @relationship
(
boy_id,
girl_id,
date_started,
date_ended
)(
SELECT 1, 1, '2007/01/01', NULL UNION ALL
SELECT 1, 3, '2004/09/15', '2005/06/15' UNION ALL
SELECT 2, 1, '2006/05/14', '2006/05/23'
);
UPDATE
b
SET
b.is_stud = 1
FROM
@boy b
INNER JOIN
@relationship r
ON
b.id = r.boy_id
INNER JOIN
@girl g
ON
(
r.girl_id = g.id
AND
g.name = 'Winona Ryder'
)
;
SELECT
id,
name,
is_stud
FROM
@boy
;