sql server 2008 management studio not checking the syntax of my query

by

It is taking the value of hs_id from the outer query.

It is perfectly valid to have a query that doesn’t project any columns from the selected table in its select list.

For example

select 10 from HotelSupplier where id = 142

would return a result set with as many rows as matched the where clause and the value 10 for all rows.

Unqualified column references are resolved from the closest scope outwards so this just gets treated as a correlated sub query.

The result of this query will be to delete all rows from Photo where hs_id is not null as long as HotelSupplier has at least one row where id = 142 (and so the subquery returns at least one row)

It might be a bit clearer if you consider what the effect of this is

delete from Photo  where Photo.hs_id  in (select Photo.hs_id)

This is of course equivalent to

delete from Photo where Photo.hs_id = Photo.hs_id

By the way this is far and away the most common “bug” that I personally have seen erroneously reported on Microsoft Connect. Erland Sommarskog includes it in his wishlist for SET STRICT_CHECKS ON

More Related Contents:

  1. SQL Server 2008: how do I grant privileges to a username?
  2. Converting Select results into Insert script – SQL Server
  3. Efficiently convert rows to columns in sql server
  4. Conditional WHERE clause in SQL Server
  5. Multi-statement Table Valued Function vs Inline Table Valued Function
  6. SQL Server Insert if not exists
  7. GROUP BY to combine/concat a column [duplicate]
  8. How to set table name in dynamic SQL query?
  9. Copy data into another table
  10. How do I see active SQL Server connections?
  11. EXISTS vs JOIN and use of EXISTS clause
  12. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  13. Saving changes after table edit in SQL Server Management Studio
  14. Logging ALL Queries on a SQL Server 2008 Express Database?
  15. Why is a UDF so much slower than a subquery?
  16. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  17. Alternate of lead lag function in SQL Server 2008
  18. How to get export output in “real” CSV format in SQL Server Management Studio?
  19. UNIX_TIMESTAMP in SQL Server
  20. Generate random int value from 3 to 6
  21. Add emoji / emoticon to SQL Server table
  22. SQL Server SELECT INTO @variable?
  23. How do I change db schema to dbo
  24. How to subtract 30 days from the current date using SQL Server
  25. Count records for every month in a year
  26. SQL Server: +(unary) operator on non-numeric Strings
  27. Use one CTE many times
  28. How to Select Every Row Where Column Value is NOT Distinct
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment