473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

UPDATE Statement Involving Tables in Different Databases


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.Car = 'Ferrari'
FROM A.dbo.tblCars INNER JOIN B.dbo.tblHouses ON
A.dbo.tblCars.RecID = B.dbo.tblHouses.RecID
WHERE (B.dbo.tblHouses = 'Mansion')

Feb 22 '06 #1
7 24653
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)."

Feb 22 '06 #2
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'
ITransactionJoin::JoinTransaction 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?

Feb 24 '06 #3
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'
ITransactionJoin::JoinTransaction 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****@sommarskog.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
Feb 24 '06 #4

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_ORDER_LINE SET MFG.dbo.PURC_ORDER_LINE.USER_1 =
'Vendor'
WHERE ROWID IN
(SELECT MFG.dbo.PURC_ORDER_LINE.ROWID FROM
Status.dbo.tblPOBaselineDueDates INNER JOIN MFG.dbo.PURC_ORDER_LINE ON
Status.dbo.tblPOBaselineDueDates.PURC_ORDER_ID =
MFG.dbo.PURC_ORDER_LINE.PURC_ORDER_ID AND
Status.dbo.tblPOBaselineDueDates.LINE_NO =
MFG.dbo.PURC_ORDER_LINE.LINE_NO INNER JOIN
Status.dbo.qryPODueDates ON
MFG.dbo.PURC_ORDER_LINE.PURC_ORDER_ID =
Status.dbo.qryPODueDates.PURC_ORDER_ID AND
MFG.dbo.PURC_ORDER_LINE.LINE_NO = Status.dbo.qryPODueDates.LINE_NO AND
Status.dbo.tblPOBaselineDueDates.BaselineDueDate =
Status.dbo.qryPODueDates.CurrentDueDate
WHERE (MFG.dbo.PURC_ORDER_LINE.USER_1 LIKE 'Aut%'))

Mar 12 '06 #5
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.PURC_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.

Mar 12 '06 #6
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.tbl

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_ORDER_LINE SET MFG.dbo.PURC_ORDER_LINE.USER_1 =
'Vendor'
WHERE ROWID IN
(SELECT MFG.dbo.PURC_ORDER_LINE.ROWID FROM
Status.dbo.tblPOBaselineDueDates INNER JOIN MFG.dbo.PURC_ORDER_LINE ON
Status.dbo.tblPOBaselineDueDates.PURC_ORDER_ID =
MFG.dbo.PURC_ORDER_LINE.PURC_ORDER_ID AND
Status.dbo.tblPOBaselineDueDates.LINE_NO =
MFG.dbo.PURC_ORDER_LINE.LINE_NO INNER JOIN
Status.dbo.qryPODueDates ON
MFG.dbo.PURC_ORDER_LINE.PURC_ORDER_ID =
Status.dbo.qryPODueDates.PURC_ORDER_ID AND
MFG.dbo.PURC_ORDER_LINE.LINE_NO = Status.dbo.qryPODueDates.LINE_NO AND
Status.dbo.tblPOBaselineDueDates.BaselineDueDate =
Status.dbo.qryPODueDates.CurrentDueDate
WHERE (MFG.dbo.PURC_ORDER_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_ORDER_LINE
SET USER_1 = 'Vendor'
WHERE ROWID IN (SELECT MFG.ROWID
FROM Status.dbo.tblPOBaselineDueDates DD
JOIN MFG.dbo.PURC_ORDER_LINE MFG
ON DD.PURC_ORDER_ID = MFG.PURC_ORDER_ID
AND DD.LINE_NO = MFG.LINE_NO
JOIN Status.dbo.qryPODueDates qDD
ON MFG.PURC_ORDER_ID = qDD.PURC_ORDER_ID
AND MFG.LINE_NO = qDD.LINE_NO
AND DD.BaselineDueDate = qDD.CurrentDueDate
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****@sommarskog.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
Mar 12 '06 #7
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!!!!!!!

Mar 20 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Kenneth Fosse | last post by:
Hi. I'm currently working on a project which involves the creation of a web page which reports selected data to customers from two back-end systems. I use ASP script language on IIS, the server...
8
by: Perre Van Wilrijk | last post by:
Hello, I have 2 ways of updating data I'm using often 1) via a cursor on TABLE1 update fields in TABLE2 2) via an some of variables ... SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE...
8
by: pb648174 | last post by:
I have a single update statement that updates the same column multiple times in the same update statement. Basically i have a column that looks like .1.2.3.4. which are id references that need to...
1
by: ChrisA | last post by:
Hi all, we are developing an internal application that involves "Timesheets", "Support" and "Project Management". Imagine that there are 3 different databases for the above scenario, under SQL...
2
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an...
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
8
by: Stephen Plotnick | last post by:
I have three forms and update one table in an Access DB with sucess in one form. In the other two forms I'm trying to do an update to a different table in the Access DB. The error I'm getting...
2
by: =?Utf-8?B?VGVycnk=?= | last post by:
Is it possible to join tables from different databases? If so, what is the format of the select statement? Both databases are on the same server. TIA, -- Terry
4
by: sganeshsvk | last post by:
sir, i want to store the same data values in two different databases at that same time in mysql using php programming. suppose any one databases data will lose then we use the other...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.