.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Update with inner join

Posted By:shankey       Posted Date: July 20, 2011    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com  

This sample code will explain how to update values based on another table.
 

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


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend