UPDATE Statement Involving Tables in Different Databases

[Altemir]

I want to create an UPDATE statement that updates a value in a table in
database A based on the results of an inner join between tables each
residing in deifferent databases.

What is the correct syntax for doing this?

The following should give you an idea of what I'm trying to do:

UPDATE A.dbo.tblCars
SET A.dbo.tblCars.C ar = 'Ferrari'
FROM A.dbo.tblCars INNER JOIN B.dbo.tblHouses ON
A.dbo.tblCars.R ecID = B.dbo.tblHouses .RecID
WHERE (B.dbo.tblHouse s = 'Mansion')

[markc600]

You can do this without a join.

UPDATE A.dbo.tblCars
SET Car = 'Ferrari'
WHERE RecID IN (SELECT RecID FROM B.dbo.tblHouses WHERE House =
'Mansion')

Using JOINs in UPDATEs may not give you the results you are expecting.

From BOL:

"The results of an UPDATE statement are undefined if the
statement includes a FROM clause that is not specified
in such a way that only one value is available for each
column occurrence that is updated (in other words, if the
UPDATE statement is not deterministic). "

[Altemir]

Mark's suggestion worked great when I ran it on a simple test, but I'm
having problems applying this methodology to my real (more complex)
application. I get the following error when running the script from
Query Analyzer:

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoi n::JoinTransact ion returned 0x8004d00a].

Note that both databases reside on the same server and I checked
Distributed Transaction Coordinator (DTC) to make sure that it's
running. What else about this kind of distributed UPDATE statement
would cause such an error?

[Erland Sommarskog]

Altemir (da***********@ gmail.com) writes:

Mark's suggestion worked great when I ran it on a simple test, but I'm
having problems applying this methodology to my real (more complex)
application. I get the following error when running the script from
Query Analyzer:

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoi n::JoinTransact ion returned 0x8004d00a].

Note that both databases reside on the same server and I checked
Distributed Transaction Coordinator (DTC) to make sure that it's
running. What else about this kind of distributed UPDATE statement
would cause such an error?

If the databases are on the same instance of SQL Server, there is no
need for any distributed transaction. If you get this message, you
have somehow introduced a four-part notation into the mix.

If the databases are on different instances, you need one, even if the
instance are on the same machine.

What applies to your case? What exact syntax did you use?

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Altemir]

Erland Sommarskog wrote:

If the databases are on the same instance of SQL Server, there is no
need for any distributed transaction. If you get this message, you
have somehow introduced a four-part notation into the mix.

If the databases are on different instances, you need one, even if the
instance are on the same machine.

What applies to your case? What exact syntax did you use?

Sorry for the long delay in following this up ...

I'm only working with one instance of both databases. Don't know what
you mean by four-part notation.

Here is the SQL statement that is generating the "'SQLOLEDB' was unable
to begin a distributed transaction" error as I stated before. As you
can see, I am attempting to perform an UPDATE of a table in database
"MFG" based on the recordset generated by joining three tables
together:
UPDATE MFG.dbo.PURC_OR DER_LINE SET MFG.dbo.PURC_OR DER_LINE.USER_1 =
'Vendor'
WHERE ROWID IN
(SELECT MFG.dbo.PURC_OR DER_LINE.ROWID FROM
Status.dbo.tblP OBaselineDueDat es INNER JOIN MFG.dbo.PURC_OR DER_LINE ON
Status.dbo.tblP OBaselineDueDat es.PURC_ORDER_I D =
MFG.dbo.PURC_OR DER_LINE.PURC_O RDER_ID AND
Status.dbo.tblP OBaselineDueDat es.LINE_NO =
MFG.dbo.PURC_OR DER_LINE.LINE_N O INNER JOIN
Status.dbo.qryP ODueDates ON
MFG.dbo.PURC_OR DER_LINE.PURC_O RDER_ID =
Status.dbo.qryP ODueDates.PURC_ ORDER_ID AND
MFG.dbo.PURC_OR DER_LINE.LINE_N O = Status.dbo.qryP ODueDates.LINE_ NO AND
Status.dbo.tblP OBaselineDueDat es.BaselineDueD ate =
Status.dbo.qryP ODueDates.Curre ntDueDate
WHERE (MFG.dbo.PURC_O RDER_LINE.USER_ 1 LIKE 'Aut%'))

[Doug]

i can't help you with the syntax, but this has GOT to be slow to run if
this table has any size to it.
is this an issue?
if so, consider changing the last line to
WHERE (left(MFG.dbo.P URC_ORDER_LINE. USER_1,3)= 'Aut'))

Consider finding logic to limit the search through PURC_ORDER_LINE . For
instance, is the vendor currently a specific wrong thing? Are they
always blank?
Anything you can do do limit the records in PURC_ORDER_LINE will speed
it all up a bunch.

[Erland Sommarskog]

Altemir (da***********@ gmail.com) writes:

I'm only working with one instance of both databases. Don't know what
you mean by four-part notation.
An example of four-part notation:

SELECT ... FROM Server.db.dbo.t bl

In this query, we retrieve data from a second server, which could be
SQL Server, Oracle or whatever. (But since it says dbo, it's likely to
be SQL Server or Sybase.
Here is the SQL statement that is generating the "'SQLOLEDB' was unable
to begin a distributed transaction" error as I stated before. As you
can see, I am attempting to perform an UPDATE of a table in database
"MFG" based on the recordset generated by joining three tables
together:
UPDATE MFG.dbo.PURC_OR DER_LINE SET MFG.dbo.PURC_OR DER_LINE.USER_1 =
'Vendor'
WHERE ROWID IN
(SELECT MFG.dbo.PURC_OR DER_LINE.ROWID FROM
Status.dbo.tblP OBaselineDueDat es INNER JOIN MFG.dbo.PURC_OR DER_LINE ON
Status.dbo.tblP OBaselineDueDat es.PURC_ORDER_I D =
MFG.dbo.PURC_OR DER_LINE.PURC_O RDER_ID AND
Status.dbo.tblP OBaselineDueDat es.LINE_NO =
MFG.dbo.PURC_OR DER_LINE.LINE_N O INNER JOIN
Status.dbo.qryP ODueDates ON
MFG.dbo.PURC_OR DER_LINE.PURC_O RDER_ID =
Status.dbo.qryP ODueDates.PURC_ ORDER_ID AND
MFG.dbo.PURC_OR DER_LINE.LINE_N O = Status.dbo.qryP ODueDates.LINE_ NO AND
Status.dbo.tblP OBaselineDueDat es.BaselineDueD ate =
Status.dbo.qryP ODueDates.Curre ntDueDate
WHERE (MFG.dbo.PURC_O RDER_LINE.USER_ 1 LIKE 'Aut%'))

Since I am unable to see the forest for all the trees, I rewrote the
query as:

UPDATE MFG.dbo.PURC_OR DER_LINE
SET USER_1 = 'Vendor'
WHERE ROWID IN (SELECT MFG.ROWID
FROM Status.dbo.tblP OBaselineDueDat es DD
JOIN MFG.dbo.PURC_OR DER_LINE MFG
ON DD.PURC_ORDER_I D = MFG.PURC_ORDER_ ID
AND DD.LINE_NO = MFG.LINE_NO
JOIN Status.dbo.qryP ODueDates qDD
ON MFG.PURC_ORDER_ ID = qDD.PURC_ORDER_ ID
AND MFG.LINE_NO = qDD.LINE_NO
AND DD.BaselineDueD ate = qDD.CurrentDueD ate
WHERE MFG.USER_1 LIKE 'Aut%')

Using aliases makes query much easier to read.

There is no apparent reference to any linked server. However, one table
in the Status database is called tbl-something and another qry-something.
Could the latter be a view that includes a table from a linked server?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Altemir]

Yes that was the problem: My select statement was querying Database A,
which involved a linked table to Database B. Although, technically, it
was not a "Linked Server" link -- it was a "Transact SQL" link -- but
the outcome appears to be the same.

I was able to re-write the UPDATE statement so that no linked tables
were involved. This actually resulted in a better design.

Thank you very much for your help, Doug, Erland, and Mark!!!!!!!