SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by aditya / UPDATE / Latest Posts

RE: UPDATE

BudaCli — Wed, 30 Sep 2009 09:41:34 GMT

Currently using this a lot with SQL 2k5 but on joins.So I guess there's no difference using keyword "JOIN" and joining using a comma

RE: UPDATE

The Dixie Flatline — Fri, 25 Sep 2009 13:20:54 GMT

[quote]Jeff has posted about this type of update before. Basically, it's a time bomb waiting to explode, and when it does it will cause extreme slowness in your server.[/quote]Thanks for the heads up! In practice, I always use the JOIN format from my first example, so the table to be updated is always the first table. I never use the FROM table1,table2 WHERE format and I [i]think[/i] it is being deprecated. But when looking at someone's legacy code I might not have known to check and change if you hadn't warned me. Another SQL-related death avoided. :-)

RE: UPDATE

Andrew Watson-478275 — Fri, 25 Sep 2009 08:45:15 GMT

[quote][b]VM-723206 (9/25/2009)[/b][hr]Very easy![/quote]To us maybe - but I'm tempted to send a link to the answer to some of our developers, who seem to be incapable of using this syntax.

RE: UPDATE

SuperDBA-207096 — Fri, 25 Sep 2009 08:07:35 GMT

Think there are a few spelling mistakes? An an extra character?update table set a1.[b]coulmn[/b]=a2.column[b]<[/b] from table1 as a1, table2 as a2 where a1.[b]colum1[/b] = a2.column2

RE: UPDATE

VM-723206 — Fri, 25 Sep 2009 07:31:55 GMT

Very easy!

RE: UPDATE

Simon-413722 — Thu, 24 Sep 2009 21:00:24 GMT

Its one of the tricky question trying to confuse the reader. I almost hit NO looking at the example (because it doesnt make sense), but come to think again, the question was asking whether its possible to have FROM in an UPDATE t-sql. Answer is YES. I told myself i would be arguing here if the answer was a NO :crazy:

RE: UPDATE

WayneS — Thu, 24 Sep 2009 18:50:41 GMT

[quote][b]Bob Hovious 24601 (9/23/2009)[/b][hr][code]update table1 set val=a2.val from table2 a2 where table1.id = a2.id[/code][/quote]Hey Bob... Jeff has posted about this type of update before. Basically, it's a time bomb waiting to explode, and when it does it will cause extreme slowness in your server.The first table in the from clause should [u]always[/u] be the target table. Therefore, the correct way to write this is:[code]update table1 set val=a2.val from table1, table2 a2 where table1.id = a2.id[/code]

RE: UPDATE

Tom Garth — Thu, 24 Sep 2009 16:10:07 GMT

[quote][b]amit_adarsh (9/24/2009)[/b][hr]Yesand we can use all the DML operation while updation[/quote]I think you meant to say [i]updationing[/i].

RE: UPDATE

amit_adarsh — Thu, 24 Sep 2009 11:30:20 GMT

Yesand we can use all the DML operation while updation

RE: UPDATE

Kenneth J. Moore — Thu, 24 Sep 2009 10:43:48 GMT

I know that the intended database environment for this question was SQL Server so the answer is yes, however if running T-SQL against an Access database, the answer is no. To me technically, the correct answer is [strike]'depends'[/strike] yes. (Corrected) I just did the research and discovered that T-SQL is SQL Server specific. Access uses JET SQL. I maintain applications that can run against either sql server or access and share 95% of the sql statements. Update queries against joined tables is one area that I have to be specific to the target database.

RE: UPDATE

dpr-717046 — Thu, 24 Sep 2009 10:01:51 GMT

I almost answered No based on the example even though I use FROM in UPDATE statements all the time. I echo the earlier the suggestions that some questions are better off without a poorly written example.

RE: UPDATE

Cliff Jones — Thu, 24 Sep 2009 09:58:34 GMT

[quote][b]Aditya Kota (9/24/2009)[/b][hr]My apology guys.The example provided makes the question confusing.[/quote]Otherwhise it was a good question though.

RE: UPDATE

macrostarrphish — Thu, 24 Sep 2009 09:51:26 GMT

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 <-- [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...

RE: UPDATE

Aditya Kota — Thu, 24 Sep 2009 09:14:46 GMT

My apology guys.The example provided makes the question confusing.

RE: UPDATE

R Wong — Thu, 24 Sep 2009 09:13:46 GMT

:( I answered no because the code does not work. Oh, well!

RE: UPDATE

jensgc — Thu, 24 Sep 2009 08:50:15 GMT

[quote][b]skjoldtc (9/24/2009)[/b][hr]A suggestion for future questions. Maybe not all questions need sample code. [/quote]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. [b]But[/b] - the code should be correct, unless the purpose of the question is to spot the error.

RE: UPDATE

Adi Cohn-120898 — Thu, 24 Sep 2009 08:21:39 GMT

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

RE: UPDATE

OCTom — Thu, 24 Sep 2009 07:53:47 GMT

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:

RE: UPDATE

Cliff Jones — Thu, 24 Sep 2009 07:26:51 GMT

RE: UPDATE

Bhavesh-1094084 — Thu, 24 Sep 2009 04:17:41 GMT

[quote][b]geervani (9/24/2009)[/b][hr]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 workupdate table1 set coulmn=a2.column from table1 as a1, table2 as a2 where a1.colum1 = a2.column2[/quote]Agree.Alternatively you can also use[code="sql"]update table1 set somecolumn=a2.column from table2 as a2 where column1 = a2.column2[/code]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[code="sql"]update a1 set a1.somecolumn=a2.column from table1 as a1, table2 as a2 where a1.column1 = a2.column2[/code]

RE: UPDATE

dave.farmer — Thu, 24 Sep 2009 03:37:20 GMT

[quote][b]beck.patrick (9/24/2009)[/b][hr]So the (corrected) example code[code]update a1set col = a2.colfrom table1 as a1, table2 as a2where a1.col = a2.col[/code][/quote]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! :-DActually, I've worked in places where that would be entirely believable! :w00t:

RE: UPDATE

beck.patrick — Thu, 24 Sep 2009 01:48:50 GMT

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[code]update a1set col = a2.colfrom table1 as a1, table2 as a2where a1.col = a2.col[/code]will execute the same operation as [code]update a1set col = a2.colfrom table1 as a1 inner join table2 as a2 on a1.col = a2.col[/code]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]

RE: UPDATE

victor.olufowobi — Thu, 24 Sep 2009 01:27:09 GMT

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.column2I want my point back :-DVictor

RE: UPDATE

geervani — Thu, 24 Sep 2009 01:02:47 GMT

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 workupdate table1 set coulmn=a2.column from table1 as a1, table2 as a2 where a1.colum1 = a2.column2

RE: UPDATE

dmoldovan — Thu, 24 Sep 2009 00:28:49 GMT

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.

RE: UPDATE

The Dixie Flatline — Wed, 23 Sep 2009 23:09:55 GMT

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.[code]update table set a1.coulmn=a2.column< from table1 as a1, table2 as a2 where a1.colum1 = a2.column2[/code]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:[quote]Msg 4104, Level 16, State 1, Line 33The multi-part identifier "a1.val" could not be bound.[/quote]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.[code];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 cteselect '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 table1drop table table2[/code]Alternatively this will work as well[code]update table1 set val=a2.val from table2 a2 where table1.id = a2.id[/code]

UPDATE

Aditya Kota — Wed, 23 Sep 2009 22:34:14 GMT

Comments posted to this topic are about the item [B]UPDATE[/B]