September 23, 2009 at 11:09 pm
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
, 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
September 24, 2009 at 12:28 am
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.
September 24, 2009 at 1:02 am
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
September 24, 2009 at 1:27 am
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 😀
Victor
September 24, 2009 at 1:48 am
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]
September 24, 2009 at 3:37 am
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! 😀
Actually, I've worked in places where that would be entirely believable! :w00t:
September 24, 2009 at 4:17 am
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
September 24, 2009 at 7:26 am
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.
September 24, 2009 at 7:53 am
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:
September 24, 2009 at 8:21 am
Like others I didn’t know what to answer. I know that FROM clause can be used in an update statement, but I also noticed that the way that the example was written, it shouldn’t work. I decided to ignore the example and give the correct answer for the question only, but I can understand why many users wrote no because of the example.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 24, 2009 at 8:50 am
skjoldtc (9/24/2009)
A suggestion for future questions. Maybe not all questions need sample code.
Although some questions very well could be written without sample code I think it is generally a good idea to have code along with a T-SQL question like this. But - the code should be correct, unless the purpose of the question is to spot the error.
September 24, 2009 at 9:13 am
🙁 I answered no because the code does not work. Oh, well!
September 24, 2009 at 9:14 am
My apology guys.
The example provided makes the question confusing.
September 24, 2009 at 9:51 am
I too answered 'no' based upon the example...perhaps the example shouldn't have been given and just the question asked, but if you ran the given statement, it would certainly fail due to:
update table <--
set a1.coulmn=a2.column <-- COULMN [misspelled]
from table1 as a1, table2 as a2
where a1.colum1 = a2.column2 <-- COLUM [misspelled]
Was this ever QA'd? If I was handed this script to apply to production, I wouldn't have to see it fail to kick it back to the author and tell them to clean it up...
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply