Deleting in SQL Query Analyzer

[lexi0088]

I am trying to delete some lines in a table, however I have to delete lines that would meet to criteria. Here's what I am doing:

select * from GL11110 where YEAR1 ='2004'and PERIODID ='12'

When I get this, I get the 3 lines that need to be deleted, so I say

delete from GL11110 where YEAR1 ='2004'and PERIODID ='12'

and then I get a message saying "View or function 'GL11110' is not updatable because the modification affects multiple base tables."

How can I delete using two sets of criteria? Or is there a way to delete just what you had selected. Otherwise it would delete more lines than it should.

Please help, thank you

 

[cLFlaVA]

it seems like you're trying to delete from a view. while i'm no sql server expert, i do know that views are just structured sql selects -- they don't hold any data by themselves.

if you want to delete data, you'll have to delete from the table or tables that hold the data.

*cLFlaVA
----------------------------
[tt]tastes great, less filling.[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SQLSister]

And to expand on what cLFlaVA said, you will also need to delete from those tables in a specific order. If you have cascading deletes set up, you can delete from the main table and the related recods will also delete. If you have a PK/FK relationship but are not using casade delete, then you must delete from the related table(s) first and then the main table.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[bhuninghake]

are you trying to delete the columns/rows located in the table or just the info located in these columns/rows ?

You can delete the info using UPDATE but if your wanting to remove the columns/rows you will be doing a structure change to the table and that takes a different approach.