Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Update one table with data from another table in sql

    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 181
    Comment on it

    Update one table with data from another table :- Some time we need to update more than one column in a SQL table with values from column in another table.So we can achieve this using join. Please refer the below example:-

    Table Structure & Values In below example I have created two tables, TempStudent and TempBooks, StudentId is the primary key in TempStudent table.

    CREATE TABLE #TempStudent 
    ( 
    StudentID int NOT NULL PRIMARY KEY,
    FirstName varchar(50), 
    LastName varchar(50), 
    Email varchar(50)
    );
    
    INSERT INTO #TempStudent VALUES  (1,'Rahul', 'Joshi','xyz@gmail.com')
    INSERT INTO #TempStudent VALUES (2,'Amit', 'Joshi','abc@gmail.com')
    INSERT INTO #TempStudent VALUES (3,'Ravi', 'Sharma','uyt@gmail.com')
    INSERT INTO #TempStudent VALUES (4,'Ajit', 'Rana','yjt@gmail.com')
    
    select * from #TempStudent
    
    StudentIDFirstNameLastNameEmail
    1RahulJoshixyz@gmail.com
    2AmitJoshiabc@gmail.com
    3RaviSharmauyt@gmail.com
    4AjitRanayjt@gmail.com

    Table 2:- TempBook

    CREATE TABLE #TempBook 
    ( 
    BookID int NOT NULL PRIMARY KEY,
    StudentId int REFERENCES #TempStudent(Id),
    Title varchar(50), 
    AuthorFirstName varchar(50),
    AuthorLastName varchar(50)
    );
    
    INSERT INTO #TempBook VALUES  (1,1,'Let Us C', 'yashwant',' kanetkar')
    INSERT INTO #TempBook VALUES (2,1,'Effective C++', 'Scott',' Meyers')
    INSERT INTO #TempBook VALUES (3,2,'The C++ Cookbook', 'Jonathan',' Turkanis')
    INSERT INTO #TempBook VALUES (4,4,'Design Patterns', 'Steve',' McConnell')
    
    select * from #TempBook
    
    BookIDStudentIdTitleAuthorFirstNameAuthorLastName
    11Let Us Cyashwant kanetka
    21Effective C++Scott Meyers
    32The C++ CookbookJonathan Turkanis
    44Design PatternsSteve McConnell

    Note: StudentId is Foreign key.

    If we want a Single SQL Query to update the Column FirstName and LastName in Table #TempStudent from AuthorFirstNameand" and "AuthorLastName from Table #TempBook.

    SQL Query:--

    UPDATE #TempBook SET AuthorFirstName = S.FirstName, AuthorLastName = S.LastName FROM #TempBook AS B INNER JOIN #TempStudent  AS S ON B.StudentId = S.StudentId WHERE S.StudentId IN(1,2,4)
    

    Result After Execution of SQL query:--

    BookIDStudentIdTitleAuthorFirstNameAuthorLastName
    11Let Us CRahul Joshi
    21Effective C++Rahul Joshi
    32The C++ CookbookAmit Joshi
    44Design Patternsajit rana

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: