problem updating view with instead of trigger

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nikhil Patel (donotspam_at_nospaml.com)
Date: 01/28/05 

Date: Fri, 28 Jan 2005 17:21:07 -0500

Hi all,
    I have created wv_details view that has an instead of update trigger. My
question is how can I update this view?

    When I run the following query I get an error - "View 'wv_details' has
an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
statement."

    UPDATE wv_details
 SET REFERENCE=latest.REFERENCE,
   [NOTES]=latest.NOTES,
   [TITLE]=latest.TITLE,
   [LINKACCT]=latest.LINKACCT,
   [COUNTRY]=latest.COUNTRY,
   [ZIP]=latest.ZIP,
   [EXT]=latest.EXT,
   [STATE]=latest.STATE,
   [ADDRESS1]=latest.ADDRESS1,
   [ADDRESS2]=latest.ADDRESS2,
   [MERGECODES]=latest.MERGECODES,
   [STATUS]=latest.STATUS,
   [LASTUSER]=latest.LASTUSER
  FROM wv_details latest
   JOIN wv_details
    ON latest.accountno = wv_details.accountno
    AND latest.detail = wv_details.detail
    AND latest.dear = wv_details.dear
    AND latest.recid <> wv_details.recid
    AND NOT EXISTS(SELECT TOP 1 1
        FROM wv_details old
        WHERE latest.accountno = old.accountno
        AND latest.detail = old.detail
        AND latest.dear = old.dear
        AND latest.recid <> old.recid
        AND latest.lastdatetime < old.lastdatetime
       )

So I changed this query as shown below. It does not use a FROM clause
anymore. But I get a different error with this query - "The text, ntext, and
image data types are invalid in this subquery or aggregate expression.". The
NOTES field is a text column.
UPDATE wv_details
 SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [NOTES]=(SELECT TOP 1 latest.NOTES
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [TITLE]=(SELECT TOP 1 latest.TITLE
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [LINKACCT]=(SELECT TOP 1 latest.LINKACCT
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [COUNTRY]=(SELECT TOP 1 latest.COUNTRY
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [ZIP]=(SELECT TOP 1 latest.ZIP
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [EXT]=(SELECT TOP 1 latest.EXT
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [STATE]=(SELECT TOP 1 latest.STATE
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [MERGECODES]=(SELECT TOP 1 latest.MERGECODES
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [STATUS]=(SELECT TOP 1 latest.STATUS
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     ),
   [LASTUSER]=(SELECT TOP 1 latest.LASTUSER
     FROM wv_details latest
     WHERE latest.accountno = wv_details.accountno
     AND latest.detail = wv_details.detail
     AND latest.dear = wv_details.dear
     AND latest.recid <> wv_details.recid
     AND NOT EXISTS(SELECT TOP 1 1
         FROM wv_details old
         WHERE latest.accountno = old.accountno
         AND latest.detail = old.detail
         AND latest.dear = old.dear
         AND latest.recid <> old.recid
         AND latest.lastdatetime < old.lastdatetime
        )
     )

So my question is how can I update this view?

Thanks...
-Nikhil

Relevant Pages

  • Re: problem updating view with instead of trigger
    ... You reference the view as if it's either an "inserted" or "deleted" ... You're are doing aggregation on the blob which is not allowed (by MS ... > I have created wv_details view that has an instead of update trigger. ... But I get a different error with this query - "The text, ntext, ...
    (microsoft.public.sqlserver.programming)
  • Re: agregate function in subquery
    ... removed the GROUP BY statement from the primary query everthing worked. ... I need to agregate in the subquery because I ... GROUP BY AEName ... or apply an aggregate function to it: ...
    (microsoft.public.access.queries)
  • Re: Complex Subquery: ... FROM (TRANSFORM.... Syntax error
    ... While technically you can use a crosstab query as a subquery, ... you cannot use the column header and value results in the parent query. ... >> I need to sort the results based upon an Aggregate. ...
    (microsoft.public.access.queries)
  • Re: Compare record count in table1 to qty in table2?
    ... "An aggregate may not appear in the WHERE clause unless it is in a subquery ... Here is a query I'm trying to use and, of course, it does not work: ...
    (comp.databases.ms-sqlserver)
  • Re: Mass Update on Table with Trigger
    ... an update trigger (which updates the field whenever a ... As a result I'm getting an error: "Subquery ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)