Very Computer

Hello there,
I am running SQl_server 6.5  through ODBC from a C++ application.
I tryed to execute the following SQl statement :

UPDATE tableA, tableB
 SET  tableA.UserName = tableB.NewName
 WHERE tableA.Name = tableB.OldName;

but it doesn't work with SQL_Server. It works fine with MS-Access.
I know that It is not a stadard SQL statement but I cannot figure out how to
convert it into standard SQL without using cursors.

So I was wondering if someone can help me out with the followings:

1. how can I convert the aforementiond statement into standard SQL
2. How can I use cursors with ODBC

Thanks a lot.

Pierluigi.

whats the problem exactly, it won't return an error but
the update is not made ??

check if your auto-commit for your particular session
is set to on (just guessing ...)
+
there rings a bell: mssql could have an extended UPDATE statement:
check out the sql-books(look at the examples..)

hth

Try...

UPDATE tableA
SET tableA.UserName =
        ( SELECT tableB.NewName
        FROM tableB
        WHERE tableA.Name = tableB.OldName )

I hope this helps.  
--

MiddleWorld SoftWare - http://web2.ica.net/middleworld
--------------------------------------------------
He went out of his way to be good.

Quote:> Hello there,
> I am running SQl_server 6.5  through ODBC from a C++ application.
> I tryed to execute the following SQl statement :

> UPDATE tableA, tableB
>  SET  tableA.UserName = tableB.NewName
>  WHERE tableA.Name = tableB.OldName;

> but it doesn't work with SQL_Server. It works fine with MS-Access.
> I know that It is not a stadard SQL statement but I cannot figure out how
to
> convert it into standard SQL without using cursors.

> So I was wondering if someone can help me out with the followings:

> 1. how can I convert the aforementiond statement into standard SQL
> 2. How can I use cursors with ODBC

> Thanks a lot.

> Pierluigi.

says...

Quote:> Hello there,
> I am running SQl_server 6.5  through ODBC from a C++ application.
> I tryed to execute the following SQl statement :

> UPDATE tableA, tableB
>  SET  tableA.UserName = tableB.NewName
>  WHERE tableA.Name = tableB.OldName;

update tableA
from   tableA a join tableB b on a.Name = b.OldName
set    a.UserName = b.NewName

Semicolons are not recognized or used in MS's T-SQL.
--
Ben McEwan, President, Geist, LLC

Isn't the from clause supposed to be after the set and the where
after the from to do the join?  This is the way I've always done it.

-my