Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE


UPDATE

Author
Message
Aditya Kota
Aditya Kota
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 473
Comments posted to this topic are about the item UPDATE
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
I thought this was a good question because I've run across a number of people who didn't realize it was possible, but the example troubles me. It just won't work. I almost answered no, thinking it was a trick question. The example really needs to be cleaned up.


update table
set a1.coulmn=a2.column<
from table1 as a1, table2 as a2
where a1.colum1 = a2.column2



First, just saying UPDATE TABLE will throw an error. It should say UPDATE [TABLE], unless it is a typo. I am assuming it's a typo, just like the typos in spelling "column" and the extra < at the end of one line. If not, it reads as if there were three tables: "table", "table1" and "table2"... which won't work either.

We are supposed to be updating the table specified in the first line, but instead the code tries to set a value of a column that was aliased in the FROM (TABLE1 as A1). I've never seen that format so I cleaned it up at little and tested it. It throws the following error:

Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "a1.val" could not be bound.


The only columns which can be updated are those belonging to the table which is the subject of the UPDATE. That table's name is specified immediately after the word UPDATE, and not necessarily after the FROM. Since only one target table can be the target, the names of the target columns do not need to be qualified, although the source column should be.

Two correct formats for using FROM with an UPDATE are found in the examples below.


;with cte (id, val) as (
select 1,'A' union all
select 2,'B' union all
select 3,'C'
)
select *
into [table1]
from cte

;with cte (id, val) as (
select 1,'X' union all
select 2,'Y' union all
select 3,'Z'
)
select *
into [table2]
from cte

select 'Table1',* from [table1]
select 'Table2',* from [table2]

select 'NOW THE UPDATE HAPPENS' as [GASP!!!]

------------------------------------------------------------------
----------- proper format for using a FROM clause in an update
------------------------------------------------------------------
update table1
set val=a2.val
from table1 a1
join table2 a2 on a1.id = a2.id

----------- (yes, i used a join instead of tablelist/where)
------------------------------------------------------------------

select 'Table1',* from [table1]

drop table table1
drop table table2




Alternatively this will work as well


update table1
set val=a2.val
from table2 a2
where table1.id = a2.id



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
dmoldovan
dmoldovan
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1454
I extensively used this way of updating while working on SQL 2000. Now I prefer to use a CTE or, if dealing with SQL 2008, a MERGE statement.
geervani
geervani
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 10
The answer for the question is YES. FROM clause can be used in UPDATE query. But the example query given is wrong.

something like this will work
update table1
set coulmn=a2.column
from table1 as a1, table2 as a2
where a1.colum1 = a2.column2
victor.olufowobi
victor.olufowobi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 92
As geervani indicated you can definitely use FROM clause in UPDATE but not in the example given.

I would do:

update a1
Set a1.column= a2.column
From table as a1 inner join table2 as a2
On a1.column1=a2.column2

I want my point back :-D

Victor
beck.patrick
beck.patrick
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 52
You can also use the FROM in SELECT, INSERTS, DELETE statements, because it is just another way to join tables.

So the (corrected) example code


update
a1
set
col = a2.col
from
table1 as a1,
table2 as a2
where
a1.col = a2.col



will execute the same operation as


update
a1
set
col = a2.col
from
table1 as a1
inner join table2 as a2
on a1.col = a2.col



When running operations with a big amount of rows, the second should have the better performance.
[EDIT]
This is because statement no.1 will first select all rows on both tables and then matches them with the where-clause. Statement no.2 will first match the tables with the join-clause and then select the matching rows. The performance will increase when the clause uses an index (i.e. primary key).
[/EDIT]
dave.farmer
dave.farmer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1153 Visits: 768
beck.patrick (9/24/2009)

So the (corrected) example code


update
a1
set
col = a2.col
from
table1 as a1,
table2 as a2
where
a1.col = a2.col




This will update table1.col to with the value of table2.col when they are already the same... the obvious conclusion is that the columns in the original question (coulmn, colum1, column and column2) are not typos! :-D
Actually, I've worked in places where that would be entirely believable! w00t
Bhavesh-1094084
Bhavesh-1094084
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 110
geervani (9/24/2009)
The answer for the question is YES. FROM clause can be used in UPDATE query. But the example query given is wrong.

something like this will work
update table1
set coulmn=a2.column
from table1 as a1, table2 as a2
where a1.colum1 = a2.column2


Agree.

Alternatively you can also use


update table1
set somecolumn=a2.column
from table2 as a2
where column1 = a2.column2



point here is if you dont specify alias for table to be updated in the from clause you can use it as table name reference. However, if you do specify alias for table1 in table from clause then you must reference the table with alias.

geervani: In your statement just to keep t-sql cleaner I would use following


update a1
set a1.somecolumn=a2.column
from table1 as a1, table2 as a2
where a1.column1 = a2.column2



Bhavesh
.NET and SQL Server Blog
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
geervani (9/24/2009)
The answer for the question is YES. FROM clause can be used in UPDATE query. But the example query given is wrong.

something like this will work
update table1
set coulmn=a2.column
from table1 as a1, table2 as a2
where a1.colum1 = a2.column2


Yes, I wasn't sure which way to answer since the syntax wasn't quite right.
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2817 Visits: 4152
A suggestion for future questions. Maybe not all questions need sample code. A simple question such as "Can you use the FROM clause in a T-SQL update statement?" would have been sufficient.

I answered yes and never looked at the code.

Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search