• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 949
  • Last Modified:

SQL Server 2005--UPDATE from SELECT statement

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
niceguy971
Asked:
niceguy971
4 Solutions
 
lwadwellCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
jogosCommented:
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
 
keyuCommented:
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
 
niceguy971Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now