MySQL: Problem using IN() with UPDATE()

[Forbes]

Hi, I’ve written something to batch update a load of rows, but the IN() function doesn’t appear to be working with the UPDATE:

SET GLOBAL group_concat_max_len=30000;
CREATE TEMPORARY TABLE bookings_temporary AS (

	SELECT
		bookings_clients_options_data.bookings_client_option_data_id AS id

	FROM
		bookings_clients_options

	INNER JOIN
		bookings_attendees
		ON (bookings_clients_options.client_id = '3')

	INNER JOIN bookings_clients_options_data ON (bookings_clients_options_data.bookings_client_option_id = '4')
		AND (bookings_clients_options.bookings_client_option_id = bookings_clients_options_data.bookings_client_option_id)
		AND (bookings_clients_options_data.booking_attendee_id = bookings_attendees.booking_attendee_id)
		AND (bookings_clients_options_data.value = 'Y')

);

UPDATE bookings_clients_options_data
SET status_booking='sent'
WHERE bookings_client_option_data_id IN (

	SELECT GROUP_CONCAT(id ORDER BY id) FROM bookings_temporary WHERE id > 0

);

DROP TABLE bookings_temporary

Instead, it updates just the first row.

For reference:

• The temporary table does contain the required rows.
• The SELECT within UPDATE is supplying the required rows.
• Someone said that IN() might be wrapping the entire string in single
  quotes, but there appears to be no option to stop that, if it is the
  case.
• I’ve tried GROUP_CONCAT(CONCAT(‘'’, id, ‘'’) ORDER BY id) but that
  doesn’t yield a single row update.

Any ideas or suggestions would be much appreciated!

[r937]

ditch the GROUP_CONCAT – this produces a string

[Forbes]

Success!

Just out of interest, how is that able to work when IN appears to require comma-separated data?

[r937]

you are right – IN requires either a comma-separated list of values or a subquery

what you’re giving it is a single character string (which just happens to be made up of characters consisting of numbers and commas)

here’s what you want –

UPDATE bookings_clients_options_data SET status_booking = 'sent' WHERE bookings_client_option_data_id IN ( SELECT id FROM bookings_temporary WHERE id > 0 )

i know this might seem counter-intuitive, but the subquery used in this IN produces a column of values, which functions the same as an enumerated list of values as in this example –

UPDATE bookings_clients_options_data SET status_booking = 'sent' WHERE bookings_client_option_data_id IN ( 937, 9, 3, 7 )

note in passing that the values don’t have to be in any order