CODEWITHSUNDEEP
mysql
Joey
Joey

Reputation: 1

Mysql use IN with subquery

I am currently trying to execute the following query, but mySQL is not supporting it.

UPDATE `TABLE 1` SET `batchid`=1 WHERE `id` 
IN (SELECT `id` FROM `TABLE 1` 
    WHERE `postcode` BETWEEN "3699*" 
     AND "3900*" AND `telstriplengte`='10' LIMIT 1);

I've been fiddling around with JOINS but can't seem to get it right. My question is if someone could point me in the right direction.

Upvotes: 0

Views: 30

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

This is your query:

UPDATE `TABLE 1`
    SET `batchid`=1
    WHERE `id` IN (SELECT `id`
                   FROM `TABLE 1`
                   WHERE `postcode` BETWEEN "3699*" AND "3900*" AND
                         `telstriplengte`='10'
                   LIMIT 1);

The specific problem with this query is that you are referring to the table being updated in the subquery. In this case -- assuming that id i unique, you don't need the subquery at all:

UPDATE `TABLE 1`
    SET `batchid`=1
    WHERE `postcode` BETWEEN "3699*" AND "3900*" 
    AND `telstriplengte` = '10'
    LIMIT 1;

Normally when using LIMIT you also have an ORDER BY. Not required, but any row could be updated that matches the conditions, and I'm guessing you want the one with the smallest id.

Upvotes: 2

Cyrare
Cyrare

Reputation: 31

Can't you pass directly all in one query like that?

UPDATE `TABLE 1` SET `batchid`=1
WHERE `postcode` BETWEEN "36990" AND "39009" AND `telstriplengte`='10' LIMIT 1;

Upvotes: 3

Related Questions