update table a from table b

update table a from table b

Post by clamfuddl » Thu, 19 Jun 2003 04:25:11



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;

********

 
 
 

update table a from table b

Post by Don R. Watter » Thu, 19 Jun 2003 04:33:47


Hey Bryan,

     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;

> ********


 
 
 

update table a from table b

Post by clamfuddl » Thu, 19 Jun 2003 04:53:56


Thank you, Don - perfect!

Bryan



> Hey Bryan,

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



> > 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;

> > ********

 
 
 

1. updating same table with trigger(use update or deleted tables)

I have a table will call meds. It has a uniqid field, a status field, and 2 date fields. If the record is updated and the 2 date fields are set to be equal(cancelling the order) I wanted an update trigger to fire to update the status field of this record to be set to 3.  I tried referencing both the deleted or the inserted table in the update trigger as below but it won't work. First is this possible through a trigger, and if so what am I missing, Thanks

ON patient_medication
FOR UPDATE
AS
--used to prevent date changes for exisitng KOP orders
--created 7-2-01 tim cronin
DECLARE










from deleted

begin




from inserted

--check for reset date to cancel med

        begin
                update patient_medication
                set rec_status = 3

        end

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. Baltimore/Washington Sybase User Group Meeting

3. Update data in another table when data is updated in current table

4. Help with field validations?

5. Cached Updates QUESTION: Sorting table of updated and non-updated records

6. Indexing

7. Updating a table - adding new column w/values from 2nd table

8. MDAC 2.5 app running on MDAC 2.7 (XP)

9. Updating Master Table Using Transaction Table Ignoring Nulls

10. Trigger to update one table when records are added to another table

11. How: Update data in one table with data in another table (synchronizing)

12. Updating a table based on WHERE statements stored in a other table

13. Updating a table with values from another table