Update field on joined databases

[JimIssI]

I have two databases with like structures but different data in one field. I need to update DB1 with data from DB2. I used;

UPDATE db1.dbo.table
SET db1.dbo.table.field3 = db2.dbo.table.field3
WHERE db1.dbo.table.field1 = db2.dbo.table.field1

Anty help is appreciated.

Jim

 

[THoey]

I am not especially good with joins yet, but I am curious, what is the "dbo" in "UPDATE db1.dbo.table"?

Normally, I would just write the following:

UPDATE db1.table 
SET db1.table.field3 = db2.table.field3
WHERE db1.table.field1 = db2.table.field1

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[grega]

I use Sybase, and in that instance, "dbo" is the DataBase Owner. It's just another level of definition which can be used when referring to a particular table.

e.g. If user greg owned a table mytab in database mydb, it could be referred to as mydb.greg.mytab, or as is usually the case, mydb..mytab.

Greg.

 

[JimIssI]

My understanding of Sybase SQL is that the dbo ref is required to id the owner of the db file to ISQL.

Any ideas how to post the changes?

Jim

 

[fousek]

Jim

To answer your original question, you need:

UPDATE db1.dbo.table
SET db1.dbo.table.field3 = db2.dbo.table.field3
FROM db1.dbo.table, db2.dbo.table
WHERE db1.dbo.table.field1 = db2.dbo.table.field1

By the way, when the owner is "dbo", the "dbo" is optional. So, you could refer to "db1.dbo.table" as "db1..table" (and if you're in db1, you can just call it "table&quot.

Hope this helps!

Palmer

 

[JimIssI]

Palmer,

I tried the string with no success or errors. I understand the ownership issues to some degree and realize I am safe including the full path in code.

UPDATE db1.dbo.table
SET db1.dbo.table.field3 = db2.dbo.table.field3
FROM db1.dbo.table, db2.dbo.table
WHERE db1.dbo.table.field1 = db2.dbo.table.field1

The above action is a simple process in Access but I am stumped in the Sybase SQL arena. When I connect to the tdb files, I first connect to db1 and then db2. This action causes db2 to connect under db1. Is this proper? Both tdb files are connected and accessible from ISQL.

 

[MikeLacey]

try:

UPDATE db1.dbo.table
SET db1.dbo.table.field3 = (select db2.dbo.table.field3 from db2.dbo.table where db1.dbo.table.field1 = db2.dbo.table.field1)
;


Mike
michael.j.lacey@ntlworld.com

 

[JimIssI]

some days it just does'nt pay....Tried the string;

UPDATE db1.dbo.table
SET db1.dbo.table.field3 = (select db2.dbo.table.field3 from db2.dbo.table where db1.dbo.table.field1 = db2.dbo.table.field1);

Works fine for a single record, fails with many records.
I'm going to try using the string in a loop with a next rec command.


Jim