Using UPDATE to copy columns from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using UPDATE to copy columns from another table

Author

Topic

ababulev
Starting Member

2 Posts

Posted - 2002-04-26 : 19:16:03

Such a simple task turned out to be quite a problem.
I was using tclodbc 2.3 to access Migrosoft SQL Server over ODBC. After the extended Web exploration, confirmed by a couple of AQL Server references and one of the articles posted here, I had constructed a query in the form:

UPDATE tbl1
SET tbl1.colName = tbl2.val
FROM tbl2
WHERE tbl1.key = tbl2.key

which didn't work.
Microsoft ODBC driver had generated the error trying to combine tbl2.val and FROM. I was trying to use all types of brackets to resolve the problem with no success. When I was looking into the different sources for SQL UPDATE syntax, FROM clause had been missing in the most of them. Microsoft sources stated that FROM clause could be included into UPDATE after SET.
I don't know whether the problem lies in tclodbc 2.3, Microsoft ODBC Driver or SQL Server. After exessive experimenting with the query, the following format worked out for me (quite surprisingly):

UPDATE tbl1 t1, tbl2 t2
SET tbl1.colName = tbl2.val
WHERE tbl1.key = tbl2.key

Edited by - merkin on 04/26/2002 21:27:48

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-26 : 21:30:08

Sorry about the edit. You posted in the wrong forum.

I would change the query to this

UPDATE tbl1
SET tbl1.colName = tbl2.val
FROM tbl1
INNER JOIN tbl2 ON tbl2.key = tbl1.key

There is an article about this technique here www.sqlteam.com/item.asp?item=3876

Damian

ababulev
Starting Member

2 Posts

Posted - 2002-04-29 : 12:25:18

The problem with this query is that I couldn't use FROM clause in the UPDATE statement - ODBC driver would generate an error.

Andrei.

quote:

Sorry about the edit. You posted in the wrong forum.

I would change the query to this

UPDATE tbl1
SET tbl1.colName = tbl2.val
FROM tbl1
INNER JOIN tbl2 ON tbl2.key = tbl1.key

There is an article about this technique here www.sqlteam.com/item.asp?item=3876

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-29 : 12:37:52

You may need to use another ODBC driver then, because that syntax is fully supported in SQL Server since at least version 6.5. If you're using an earlier version of SQL Server, then it may not be supported.

You can get the latest Microsoft Data Drivers from this site:

http://www.microsoft.com/data/download.htm

You will want to get MDAC 2.6 SP1, or MDAC 2.7 if you are using Windows XP. Once you install these components they will update the ODBC driver and should solve the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources