problem updating view with instead of trigger

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...
-NikhilThere are quite a few issues:
1. You reference the view as if it's either an "inserted" or "deleted"
virtual table. It's not so.
2. You have an Instead Of trigger on the view, you should be updating the
base table within that trigger. You have full access the virtual tables
there.
3. You cannot do (update obj set col =(select top 1 lob_col from ...)).
You're are doing aggregation on the blob which is not allowed (by MS
design).
4. Even if (col=select top 1 lob) is allowed, this update is going to cost
you royally.
You have been posting for a while here. You know it would be easier if you
post ddl+sample data/code+expected output, it would be easier to help you.
http://groups.google.co.uk/groups?h...er+Nikhil+Patel
http://www.aspfaq.com/etiquette.asp?id=5006
-oj
"Nikhil Patel" <donotspam@.nospaml.com> wrote in message
news:eI6SpeYBFHA.2676@.TK2MSFTNGP12.phx.gbl...
> 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
>