Very Computer

I do a lot of selection queries, but not many update queries and have
a list of clients in one table and some name and address updates in
a secondary table that I want to use as updates.
In my sql below, I want to update the invClients table with the information
from the invTEST table.  The two tables are identical in structure.

When I attempt to run this SQL, I get an invalid column name or table name
error.  I can't figure out what I'm doing wrong here.

Any help would be greatly appreciated!

Thanks in advance,

Bryan

********
update invclients

set invclients.name = invtest.name,
    invclients.streetno = invtest.streetno,
    invclients.frac = invtest.frac,
    invclients.predir = invtest.predir,
    invclients.streetname = invtest.streetname,
    invclients.suffix = invtest.suffix,
    invclients.postdir = invtest.postdir,
    invclients.apt = invtest.apt,
    invclients.city = invtest.city,
    invclients.state = invtest.state,
    invclients.zip5 = invtest.zip5,
    invclients.zip4 = invtest.zip4
where invclients.phone = invtest.phone;

********

     This is sometimes referred to as a correlated update.  The example in
BOL is at "Use the UPDATE statement using information from another table" in
the UPDATE page for Transact-SQL Reference.  Try this:

update invclients
set invclients.name = invtest.name,
     invclients.streetno = invtest.streetno,
     invclients.frac = invtest.frac,
     invclients.predir = invtest.predir,
     invclients.streetname = invtest.streetname,
     invclients.suffix = invtest.suffix,
     invclients.postdir = invtest.postdir,
     invclients.apt = invtest.apt,
     invclients.city = invtest.city,
     invclients.state = invtest.state,
     invclients.zip5 = invtest.zip5,
     invclients.zip4 = invtest.zip4
from invclients, invtest
where invclients.phone = invtest.phone;

HTH

--
Regards,

Don R. Watters
Data Group Manager
PhotoWorks, Inc.

Quote:> Hi!

> I do a lot of selection queries, but not many update queries and have
> a list of clients in one table and some name and address updates in
> a secondary table that I want to use as updates.
> In my sql below, I want to update the invClients table with the
information
> from the invTEST table.  The two tables are identical in structure.

> When I attempt to run this SQL, I get an invalid column name or table name
> error.  I can't figure out what I'm doing wrong here.

> Any help would be greatly appreciated!

> Thanks in advance,

> Bryan

> ********
> update invclients

> set invclients.name = invtest.name,
>     invclients.streetno = invtest.streetno,
>     invclients.frac = invtest.frac,
>     invclients.predir = invtest.predir,
>     invclients.streetname = invtest.streetname,
>     invclients.suffix = invtest.suffix,
>     invclients.postdir = invtest.postdir,
>     invclients.apt = invtest.apt,
>     invclients.city = invtest.city,
>     invclients.state = invtest.state,
>     invclients.zip5 = invtest.zip5,
>     invclients.zip4 = invtest.zip4
> where invclients.phone = invtest.phone;

> ********

Bryan