SQL Server: Error 4145 while performing a multiple column search

Question

I have the following query:

delete from Copies
where (id,MovieID) IN (
select distinct id,MovieID
from copies 
where type = 'dubbed' AND (id,MovieID) NOT IN (select id,MovieID from Bookings))

Which I am basically trying to delete all the 'dubbed' copies in one table of mine which were never booked/reserved. When I execute the query, it is informed that:

An expression of non-boolean type specified in a context where a condition is expected, near ','.

I know that this query runs fine in PostgreSQL, but I am experiencing problems to execute it at SQL Server 2012. What should I do here?

Are you allowed to have multiple columns in an ``IN`` expression? — acfrancis, Nov 01 '13 at 00:03
I've never seen more than one value used on the left side of an ``IN`` clause. Rewriting it as an ``EXISTS`` should do the trick. — acfrancis, Nov 01 '13 at 00:07
@acfrancis: Ad-hoc row expressions `(id, movieid)` work just fine in Postgres. [But `NOT IN` regularly surprises people when NULL values are involved in any way](http://stackoverflow.com/questions/19363481/select-rows-which-are-not-present-in-other-table/19364694#19364694). — Erwin Brandstetter, Nov 01 '13 at 00:32
@Erwin Brandstetter: The OP says it runs fine in PostgreSQL. I don't think multiple columns are allowed in SQL Server (where the OP is having trouble) but I could be wrong. — acfrancis, Nov 01 '13 at 00:34
@acfrancis: Yeah, I don't think the row expression `(id, movieid)` would work in SQL Server. — Erwin Brandstetter, Nov 01 '13 at 01:13

score 1 · Answer 1 · answered Nov 01 '13 at 00:10

1

This version should work in SQL server.

delete 
    c
from
    copies c
where
    type = 'dubbed' and
    not exists (
        select
            'x'
        from
            Bookings b
        Where 
            c.id = b.id and
            c.movieId = b.MovieId
    )

answered Nov 01 '13 at 00:10

Laurence

10,896
1
25
34

what "select 'x'" should be? – Mudkip Nov 01 '13 at 00:18
anything, it doesn't matter. I use 'x'. Some people use 1 or null. You're not interested in what is returned, just whether something is returned. – Laurence Nov 01 '13 at 00:44
The following errors are occuring: The multi-part identifier "c.id" could not be bound. The multi-part identifier "c.filmeId" could not be bound. What it could be? – Mudkip Nov 01 '13 at 00:58
I've no idea. Using Erwin's example, here's an SQLFiddle showing this statement works: http://sqlfiddle.com/#!6/cbc2c/7 – Laurence Nov 01 '13 at 01:04
@Mudkip: `filmeId`? You had `movieid` in the question. @Laurence's answer should work just fine (+1). The `DELETE c FROM copies c` part is non-standard SQL-Server syntax. – Erwin Brandstetter Nov 01 '13 at 01:06
Correct, it should be MovieID! My bad – Mudkip Nov 01 '13 at 01:15

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

That's how you should do it in Postgres to begin with:

DELETE FROM copies
WHERE  type = 'dubbed'
AND    NOT EXISTS (
   SELECT 1           -- it's irrelevant what goes here
   FROM   bookings b
   WHERE  b.id = copies.id
   AND    b.movieid = copies.movieid
   );

The same works in SQL-Server (2008 or later at least) as well.

->SQLfiddle demo.

More about the comment in above code.

edited May 23 '17 at 11:50

Community

1
1

answered Nov 01 '13 at 00:55

Erwin Brandstetter

605,456
145
1,078
1,228

SQL Server: Error 4145 while performing a multiple column search

2 Answers2