Asked  7 Months ago    Answers:  5   Viewed   39 times

As always, there will be a reasonable explanation for my surprise, but till then....

I have this query

delete from Photo  where hs_id  in (select hs_id  from HotelSupplier where id = 142)

which executes just fine (later i found out that the entire photo table was empty)

but the strange thing: there is no field hs_id in HotelSupplier, it is called hs_key!

So when i execute the last part

select hs_id  from HotelSupplier where id = 142

separately (select that part of the query with the mouse and hit F5), i get an error, but when i use it in the in clause, it doesn't!

I wonder if this is normal behaviour?

 Answers

28

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

Tuesday, June 1, 2021
 
Sauleil
answered 7 Months ago
54

I have solved this for similar problems and it need not be that the rows even be sorted:

select t1.EmpID, t1.TimeIn, t1.TimeOut, 
       datediff(minute, max(t2.TimeOut), t1.TimeIn) as minutes
from timesheet t1 left join timesheet t2 on t1.EmpID = t2.EmpID 
       and t2.TimeOut < t1.TimeIn
group by t1.EmpID, t1.TimeIn, t1.TimeOut

Let me know if this works.

Here is a sql fiddle: http://sqlfiddle.com/#!3/89a43/1

Tuesday, August 10, 2021
 
wavyGravy
answered 4 Months ago
34

COMPUTE is no longer available in SQL server 2012, thats why you are getting that error. See this page:

  • Discontinued Database Engine Functionality in SQL Server 2012

It said that:

This topic describes the Database Engine features that are no longer available in SQL Server 2012:

*Transact-SQL syntax | COMPUTE / COMPUTE BY *

Friday, August 13, 2021
 
CAMason
answered 4 Months ago
12

Assign an alias to the inner query:

SELECT
    *
FROM
    (SELECT * FROM Client) AS i
Saturday, August 28, 2021
 
Billy
answered 3 Months ago
19

If you're editing the table in the designer then it's the designer that is being pedantic. Try changing (unchecking) these options:

Tools > Designers > Prevent saving changes that require table re-creation

The designer still throws an warning dialog after unchecking that but there is also this option to uncheck:

Tools > Designers > Warn about tables affected

I believe the reason the designer has to drop tables to rename columns is because there is no SQL command to do that, and what it does instead is copy the table's data into a temp table, drop the table, create a new table with the altered column name and copy the data into it.

Monday, October 11, 2021
 
CodeCaster
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share