List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 17 2000 1:37pm
Subject:Re: SQL: looking for "IN (SELECT blabla FROM blablaTable)" in MySQL
View as plain text  
>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:

Tim> On Sat, Jan 15, 2000 at 08:45:45PM -0700, Sasha Pachev wrote:
>> Raffael Heinzer wrote:
>> > 
>> >   I'd like to delete some records in the common SQL way in MySQL:
>> > "DELETE FROM [table] WHERE [i] IN (SELECT [u] FROM [anotherTable] WHERE
>> > ...)"
>> > 
>> > I figured out that "IN (12, 13, 14)" with a list of constants works -
>> > but I need to have there something dynamically... :(
>> > 
>> > Is there any way of doing that in a different way in MySQL (or is my
>> > statement incorrect?) or do I have to put that logic into the DBClient?
>> 
>> I don't think there is a way to do this yet -- you will have to do
>> generate the constants in your program for now -- there will be a better
>> solution a few releases down the road.

Tim> I've often thought it would be interesting if it were possible to
Tim> have a GROUP function that concatenated strings.

Tim> Consider    SELECT SUM(num_field) ...
Tim> for each row that executes something like "result = result + num_field".

Tim> Now imagine that you could say something like:

Tim>             SELECT JOIN(",", string_field) ...

Tim> and for each row that would do "result = CONCAT(result, ',', string_field)"
Tim> (but do the right thing for the first row).

Tim> If that existed then you could do something like:

Tim> 	SELECT @values = JOIN(",", string_field) FROM ...;
Tim> 	DELET FROM table WHERE field IN (@values);

Tim> Seems kind'a cute.

Hi!

I have also thought about this from time to time; The major problem is
of course that the string may be very big and one have to code this
carefully.  I shall put this on the TODO if it isn't there already.

Anyway, one can't us the string in an IN clause, as the in clause
takes a list and not a string. On can of course instead use:

DELETE FROM table WHERE find_in_set(field,@values);

To do this efficienlty, we would also need to optimize find_in_set in
the same way as we do IN.

Another possibility would be to extend the parser to let you expand
variables 'inline', like defines.  This would be VERY powerful feature
as it would let you hold SQL statements (or parts thereof) in
variables, but the question is what syntax to use. Maybe something
like:

DELETE FROM table WHERE field IN (@@values);

Regards,
Monty
Thread
SQL: looking for "IN (SELECT blabla FROM blablaTable)" in MySQLRaffael Heinzer16 Jan
  • Re: SQL: looking for "IN (SELECT blabla FROM blablaTable)" in MySQLSasha Pachev16 Jan
    • Re: SQL: looking for "IN (SELECT blabla FROM blablaTable)" in MySQLTim Bunce17 Jan
      • Re: SQL: looking for "IN (SELECT blabla FROM blablaTable)" in MySQLMichael Widenius17 Jan
        • Re: SQL: looking for "IN (SELECT blabla FROM blablaTable)" in MySQLTim Bunce17 Jan
  • Re: SQL: looking for "IN (SELECT blabla FROM blablaTable)" inMySQLPaul DuBois16 Jan