SQL Server Error: "maximum number of prefixes. The maximum is 3" with subselect syntax

Question

Trying to run a cross-server update:

UPDATE asilive.Contoso.dbo.lsipos
SET PostHistorySequencenNmber = (
    SELECT TransactionNumber 
    FROM Transactions 
    WHERE Transactions.TransactionDate = 
         asilive.CMSFintrac.dbo.lsipos.TransactionDate)

Gives the error:

Server: Msg 117, Level 15, State 2, Line 5
The number name 'asilive.Contoso.dbo.lsipos' contains more than 
the maximum number of prefixes. The maximum is 3.

What gives?

---

Note: Rearranging the query into a less readable join form:

UPDATE asilive.Contoso.dbo.lsipos
SET PostHistorySequenceNumber = B.TransactionNumber
FROM cmslive.Contoso.dbo.lsipos A
    INNER JOIN Transactions B
    ON A.TransactionDate = B.TransactionDate

does not give an error.

See also

• SQL Server Error: maximum number of prefixes. The maximum is 3. with join syntax
  (Deals with join syntax; this question deals with sub-select syntax)

Solution

Yes, that is just the way it is. You can't have more than three prefixes, so you have to use an aliases when you go over 3 (mainly when joining to other servers). It's been that way since Sql Server 7 (and maybe before I can't remember on 6.5).

If you want to make your code more readable when using aliases, specify a more meaningful alias which will make it a lot easier to follow.

Example:

SELECT 

production_accounting_clients.[ClientName]

FROM Production.Accounting.dbo.Clients production_accounting_clients

Comments

[mistertodd]

My select doesn't have a problem with a four-part prefix, the update does. And the update doesn't allow an alias. So i'm still curious to see a working syntax.

[Jens Frandsen]

You can use alias with updates. Update tn set tn.x=y from tableName tn