0

How do I delete data that is not in the view, the view is made up of 8 different tables using intersect they all have 'country' column in them and I want to delete country that is not in the view (vw_Countries).

I tried this two ways and I get this error message.

Msg 207, Level 16, State 1, Line 24
Invalid column name 'vw_Countries'.

Delete from [WorldFoodProduction].[dbo].[CO2_Data]
         where country not in( vw_Countries).

Delete  [WorldFoodProduction].[dbo].[CO2_Data]
         where country not in( vw_Countries).
jarlh
  • 42,561
  • 8
  • 45
  • 63
solo
  • 9
  • 2
  • 1
    Did you mean `NOT IN (SELECT country FROM vw_Countries)`? – Phil Mar 06 '23 at 02:29
  • Does this answer your question? [Delete sql rows where IDs do not have a match from another table](https://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-from-another-table) – Phil Mar 06 '23 at 02:29
  • 1
    This is not MySQL, please tag with your actual database. – ysth Mar 06 '23 at 04:48

1 Answers1

2

Try this instead:

DELETE FROM [WorldFoodProduction].[dbo].[CO2_Data]
WHERE country NOT IN (SELECT country 
                        FROM vw_Countries)

You need to use a select statement to select the value from the view.

dtthom09
  • 311
  • 1
  • 13