Mysql – How to create Parent record on Child record creation and assign Parent ID to Child record in MySQL

It is possible to do two assignments in one IF statement.

There are several ways; I present one, using CASE statement:

SET @var1 = 20;
SET @var2 = 26;
UPDATE table1 SET
    col1=IF(
      @var1>50, 
      @var1 := @var1-col4, 
      CASE
        WHEN @var1 := @var1+col5 IS NULL THEN NULL
        WHEN @var2 := @var2 + 100 IS NULL THEN NULL
        ELSE @var1
      END
    ),
col2=IF(@var2>50, @var2 := @var2-col6, @var2);

The idea is to create a CASE statement where first WHEN clauses fail. They fail because we want them to, but along they way they get evaluated.

But in order for them to get evaluated, they must first computer the assignment expression, such as @var1 := @var1+col5 and @var2 := @var2 + 100.

CASE expressions work from first WHEN clause to the next one, and must work in this order.

Please consider reading more about it in my presentation of last year's Percona Live London (2011) conference. For referece, relevant slides are 13 - 15: Programmatic queries: things you can code with sql

There are various ways to solve this problem, but with a composite index on (album_id, artist_id) most DBMS will manage to use only this index (a full index scan) with the following query and thus be quite efficient:

SELECT album_id
FROM track
GROUP BY album_id
HAVING MIN(artist_id) < MAX(artist_id) ;

If you also want the number of artists per album, you can add COUNT(DISTINCT artist_id) in the select list.

A less obfuscated alternative condition is to use (which could be less efficient than the above MIN < MAX method):

HAVING COUNT(DISTINCT artist_id) > 1 ;