Update using Inner join
This article will explain how to
update values based on another table.
Create Student table
CREATE TABLE
#Student (NAME
VARCHAR(10), Age INT, Result varchar(10))
Insert some data in student table
INSERT INTO
#Student
(NAME, Age, Result)
SELECT 'Raj', 21,''
UNION ALL
SELECT 'Maaz', 22, ''
UNION ALL
SELECT 'Mack', 21, ''
UNION ALL
SELECT 'Razz', 24, ''
UNION ALL
SELECT 'Laz', 25, ''
UNION ALL
SELECT 'Daz', 22, ''
Create Marks Table
CREATE TABLE
#Marks(NAME VARCHAR(10),Total int)
Insert some data in Marks table
INSERT INTO
#Marks
(NAME, Total)
SELECT 'Raj',55
UNION ALL
SELECT 'Maaz', 44
UNION ALL
SELECT 'Mack', 33
UNION ALL
SELECT 'Razz', 67
UNION ALL
SELECT 'Laz', 87
UNION ALL
SELECT 'Daz', 99
Select data from both table to view what is
inserted
SELECT * FROM
#Student
SELECT * FROM
#Marks
Update with inner join
Here we are going to set result
of each student based on their marks. If marks are greater than 50 then student
is considered as pass otherwise it is false.
UPDATE #Student SET
Result=CASE
WHEN #Marks.Total > 50
THEN 'Pass'
ELSE 'Fail'
END
FROM
#Student INNER JOIN #Marks ON
#Student.NAME = #Marks.NAME
Select data from both table to view what result are set by
update with inner join query
SELECT * FROM
#Student
SELECT * FROM
#Marks
You can wirte more case to meet you requirements like give
excellent, Distinction , First class, Second class etc grades based on their
marks