• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Delete SQL statement

I am using Query analyzer which i use to maipulate and update numerous database tables found on a server. I have a table with stock numbers e.g C87676-001 that also has numerous other columns, an example of one is the planner notes column. These stock numbers occur 2 or 3 times over and the copys have incomplete information that needs to be deleted. e.g. One entry of C87676-001 may have all information available in the columns then the next entry of C87676-001 may have the same info in every column except the planner notes column when it has a NULL value. This record will be the one that we delete.

I used the following statement

DELETE FROM dbo.vw_linc_mrp
WHERE Stock_Nbr like 'C87676-001' AND [Planner notes] like 'NULL'

this should work though in this case i get the following error....


The view or function 'dbo.vw_linc_mrp' is not updatable because the definition contains the TOP clause.

ideas on how to help?
0
ward20
Asked:
ward20
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
IS 'dbo.vw_linc_mrp' a table ?

SELECT * FROM iNFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'vw_linc_mrp'
0
 
arnoldCommented:
try
select * from dbo.vw_linc_mrp
WHERE Stock_Nbr like 'C87676-001' AND [Planner notes]=NULL

To see whether the rows you want deleted are returned.

prior to:
DELETE FROM dbo.vw_linc_mrp
WHERE Stock_Nbr like 'C87676-001' AND [Planner notes]=NULL
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as from the error message, the view is not updatable.
if the view returns the primary key informations, you can do this:

delete from yourtable
 where keyfield in ( select keyfield from  dbo.vw_linc_mrp
WHERE Stock_Nbr like 'C87676-001' AND [Planner notes] like 'NULL'
 )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now