Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server 2005--UPDATE from SELECT statement

Posted on 2012-08-23
5
Medium Priority
?
938 Views
Last Modified: 2012-09-29
The Below statement (#1) allows to Update Table based on other_table.

How to change statement #1 to use 3 tables--Table, other_table and other_table_Two..

so Table will be updated based on other_table_Two???

IN other words , statement #2 will contain 3 tables: Table, other_table and other_table_Two

--------------------------------------
--statement #1:
UPDATE Table
SET  Table.col1 = other_table.col1,
     Table.col2 = other_table.col2
--select Table.col1, other_table.col,Table.col2,other_table.col2, *  
FROM     Table
INNER JOIN     other_table
    ON     Table.id = other_table.id
----------------------------------------------
It's possible to do Select to check what will be updated before doing the update:
---statement #2
select Table.col1, other_table.col,Table.col2,other_table.col2, *  
FROM     Table
INNER JOIN     other_table
    ON     Table.id = other_table.id
---------------------------------------------------------

Thanks
0
Comment
Question by:niceguy971
5 Comments
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 500 total points
ID: 38327917
You can do multiple join in an UPDATE and have a WHERE clause, e.g.
UPDATE Table1 
SET  Table1.col1 = table2.col1,
     Table1.col2 = table3.col2 
FROM     Table1 
INNER JOIN  table2
    ON     Table1.id = table2.id 
INNER JOIN  table3
    ON     Table1.id = table3.id 
WHERE table2.value = 'xyz'
AND table2.amount > 0

Open in new window

You can use an in-line view
UPDATE Table1 
SET  Table1.col1 = v.col1,
     Table1.col2 = v.col2 
FROM     Table1 
INNER JOIN (select Table1.col1, table2.col,Table1.col2,table2.value,table2.amt
            FROM Table1 
            INNER JOIN  table2 
               ON     Table1.id = table2.id
            WHERE table1.colx = 'abc') v
    ON     Table1.id = v.id 
WHERE v.value = 'xyz'
AND v.amount > 0

Open in new window


or any combination of them both.
0
 
LVL 72

Assisted Solution

by:Qlemo
Qlemo earned 500 total points
ID: 38328300
If you want to transform a select into an update, you can do either
   update a
   set ...
   from tbl1 a join tbl2 b ...

Open in new window

(where a is the alias for tbl1 as used in the test select)
or, even more SELECT'ish:
   update x
   set ...
   from (select ... from tbl1 a join tbl2 b ...) x

Open in new window

The latter allows to build an UPDATE just around the same SELECT you used for getting the test results.
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 500 total points
ID: 38328391
Iwadwell answered your 3-table question.

Your select to test your query before you use the join in an update is a good technique.  In procedures with different changes after each other it's not always possible.

On a development-environment and when it's not about realy big updates another technique  can be to use that OUTPUT-clause and rollback your test-transaction.  
For more info and examples see : http://msdn.microsoft.com/en-us/library/ms177564.aspx
0
 
LVL 9

Accepted Solution

by:
keyu earned 500 total points
ID: 38335635
you can also use uotput cluse introduced in sql 2005..

http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/

UPDATE Table
SET  Table.col1 = other_table.col1,
     Table.col2 = other_table_two.col2
FROM     Table
INNER JOIN     other_table
    ON     Table.id = other_table.id
inner join other_table_two on
other_table_two .id= other_table.col2
0
 

Author Closing Comment

by:niceguy971
ID: 38447832
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question