Update query using Subquery in Sql Server

Question

I have a simple table Structure like this   Table tempData   ------------------       NAME      MARKS      ---------- -------      Narendra       80       Ravi           85       Sanjay         90     ------------------    And I also have another table names as tempDataView like this   ------------------       NAME      MARKS      ---------- -------      Narendra                Narendra                Narendra                Narendra                Ravi                    Ravi                    Sanjay                ------------------    I want to update the table tempDataView   by setting the Marks  according to the tempDataView - Name compared with tempData - Name  Yes let me show you what I tried  I tried to solve this using the Cursor and its solved perfectly  but I am finding the way to solve it using the Subquery  Here it is   Declare  name varchar 50   marks varchar 50  Declare  cursorInsert CURSOR set  cursorInsert   CURSOR FOR Select name marks from tempData OPEN  cursorInsert FETCH NEXT FROM  cursorInsert into  name  marks WHILE   FETCH STATUS   0 BEGIN UPDATE tempDataView set marks    marks where name    name FETCH NEXT FROM  cursorInsert INTO  name  marks END CLOSE  cursorInsert DEALLOCATE  cursorInsert   Actually it s like the homework for me to solve it using the Subquery

User · Answer

The title of this thread asks how a subquery can be used in an update  Here s an example of that   update  dbName   dbo   MyTable   set MyColumn   1  where                select count             from  dbName   dbo   MyTable  mt2          where             mt2 ID  gt   dbName   dbo   MyTable  ID             and mt2 Category    dbName   dbo   MyTable  Category        gt  0

User · Answer

Here in my sample I find out the solution of this  because I had the same problem with updates and subquerys   UPDATE     A SET     A ValueToChange   B NewValue FROM               Select   From C       B Where      A Id   B Id

User · Answer

you can join both tables even on UPDATE statements   UPDATE  a SET     a marks   b marks FROM    tempDataView a         INNER JOIN tempData b             ON a Name   b Name    SQLFiddle Demo   for faster performance  define an INDEX on column marks on both tables   using SUBQUERY  UPDATE  tempDataView  SET     marks                        SELECT marks            FROM tempData b            WHERE tempDataView Name   b Name              SQLFiddle Demo

User · Answer

Here is a nice explanation of update operation with some examples  Although it is Postgres site  but the SQL queries are valid for the other DBs  too  The following examples are intuitive to understand   -- Update contact names in an accounts table to match the currently assigned salesmen   UPDATE accounts SET  contact first name  contact last name         SELECT first name  last name FROM salesmen      WHERE salesmen id   accounts sales id    -- A similar result could be accomplished with a join   UPDATE accounts SET contact first name   first name                      contact last name   last name   FROM salesmen WHERE salesmen id   accounts sales id    However  the second query may give unexpected results if salesmen id is not a unique key  whereas the first query is guaranteed to raise an error if there are multiple id matches  Also  if there is no match for a particular accounts sales id entry  the first query will set the corresponding name fields to NULL  whereas the second query will not update that row at all   Hence for the given example  the most reliable query is like the following    UPDATE tempDataView SET  marks         SELECT marks FROM tempData      WHERE tempDataView Name   tempData Name

User · Answer

because you are just learning I suggest you practice converting a SELECT joins to UPDATE or DELETE joins  First I suggest you generate a SELECT statement joining these two tables   SELECT   FROM    tempDataView a         INNER JOIN tempData b             ON a Name   b Name   Then note that we have two table aliases a and b  Using these aliases you can easily generate UPDATE statement to update either table a or b  For table a you have an answer provided by JW  If you want to update b  the statement will be   UPDATE  b SET     b marks   a marks FROM    tempDataView a         INNER JOIN tempData b             ON a Name   b Name   Now  to convert the statement to a DELETE statement use the same approach  The statement below will delete from a only  leaving b intact  for those records that match by name   DELETE a FROM    tempDataView a         INNER JOIN tempData b             ON a Name   b Name   You can use the SQL Fiddle created by JW as a playground

[sql] Update query using Subquery in Sql Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-update