Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

UPDATE Expand / Collapse
Author
Message
Posted Wednesday, September 23, 2009 10:34 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 8, 2014 10:15 AM
Points: 732, Visits: 408
Comments posted to this topic are about the item UPDATE
Post #793055
Posted Wednesday, September 23, 2009 11:09 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #793059
Posted Thursday, September 24, 2009 12:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:16 PM
Points: 363, Visits: 1,321
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.
Post #793075
Posted Thursday, September 24, 2009 1:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 8, 2010 2:53 AM
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
Post #793080
Posted Thursday, September 24, 2009 1:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 21, 2012 3:07 AM
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

Victor
Post #793087
Posted Thursday, September 24, 2009 1:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 10, 2011 4:37 AM
Points: 1,111, Visits: 51
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]
Post #793094
Posted Thursday, September 24, 2009 3:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
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!
Post #793131
Posted Thursday, September 24, 2009 4:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 5:16 AM
Points: 147, Visits: 104
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
Post #793149
Posted Thursday, September 24, 2009 7:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638
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.
Post #793244
Posted Thursday, September 24, 2009 7:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 2,585, Visits: 3,842
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.

Post #793275
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse