Date: 09/14/05 (SQL Server)    Keywords: asp, sql, microsoft

Alright... imagine this scenario. Server 1 has two tables. Server 2 has views to server 1's two tables.

From Server 2, you're attempting to execute an UPDATE on said views. (No, you cannot simply update server1 and the two tables directly, that's not an option so don't ask.) If you run a single UPDATE with a WHERE clause against view 1, it'll work fine.

BUT if you add in an INNER JOIN constraint against view 2, instead of updating just the handful of records you wish to UPDATE , it'll UPDATE EVERYTHING!!!!

Example:

-- Server1
CREATE TABLE tempOne (
rowid int IDENTITY(1, 1),
myvalue int
)

CREATE TABLE tempTwo (
rowid int
)

-- Server2
CREATE VIEW viewOne AS (SELECT * FROM server1...tempOne)

CREATE VIEW viewTwo AS (SELECT * FROM server1...tempTwo)

-- Insert 5 random values into tempOne then insert '2' and '4' into tempTwo, which will later be used as a constraint

-- Server2
-- This works just fine, should return rowid 2 and 4 of viewOne.
SELECT *
FROM viewOne t1
INNER JOIN viewTwo t2
ON t1.rowid = t2.rowid

-- This is what does NOT work
UPDATE viewOne
SET myvalue = myvalue * -1
FROM viewOne t1
INNER JOIN viewTwo t2
ON t1.rowid = t2.rowid

Instead of rowid 2 and 4 being UPDATED, ALL 5 rows will be UDPATED. What GIVES?!?! I see via SQL Profiler that the query is sent from Server2 to Server1 and redefined there in a really messed up fashion. Am still trying to wrap my head around how I can rewrite this query. And no, I cannot not use the views and directly jump to Server1 because in my real scenario, it could be ServerX... I don't know until runtime, hence why we have the generic views.

Help?

--

UPDATE: Alright, I've done some more digging and figured a few things out.

First, this has NOTHING to do with views. The problem has to do with remote server UPDATE statements. I ran some more tests without views but with direct table references as a WHAT IF, and ran into the same results. But when I'd tweak the UPDATE statement slightly, I managed to generate a SQL Server error which I did a lookup on and found the following:

http://support.microsoft.com/default.aspx?scid=kb;en-us;814581

Basically the table I'm updating in question, doesn't have any PKs or UNIQUE constraints on it. My basic example, that would be easy to toss in. However the actual Production table I'm dealing with does NOT have any PKs, nor can I enforce any (don't ask, makes me want to sob). In other words, I'm basically screwed.

*sigh*

Source: http://www.livejournal.com/community/sqlserver/33825.html