UPDATE statement using the same table in subquery

Question

SELECT
    vl1.phone_number,
    vl1.first_name,
    CONCAT(
        SUBSTRING(
            (
            SELECT
                vl2.phone_number 
            FROM
                list as vl2
            WHERE
                vl2.phone_number LIKE CONCAT( SUBSTRING( vl1.phone_number FROM 1 FOR 3 ), "%" ) 
            ORDER BY
                RAND( ) 
                LIMIT 1 
            ) 
        FROM
            1 FOR 6 
        ),
        FLOOR( RAND( ) * ( 8999 ) ) + 1000 
    ) AS autogenNumber 
FROM
    list as vl1
LIMIT 1

The results I get are

phone_number | firstname | autogenNumber

The autogenNumber is generated by first searching for other numbers that share the first three digits. Then 6 digits from that number are picked and another 4 random digits are subsituted to the end.

The above sql query generates the autogen number exactly as I need it.

However, now the issue arises when I want to update the column security_phrase in this list using the similar query below.

UPDATE list as vl1
SET vl1.security_phrase = (
    CONCAT(
        SUBSTRING(
            (
                SELECT
                    vl2.phone_number 
                FROM
                    list AS vl2 
                WHERE
                    vl2.phone_number LIKE CONCAT( SUBSTRING(phone_number FROM 1 FOR 3 ), "%" ) 
                ORDER BY
                    RAND( ) 
                    LIMIT 1 
                ) 
            FROM
                1 FOR 6 
            ),
            FLOOR( RAND( ) * ( 8999 ) ) + 1000 
        ) 
    ) 
    LIMIT 10

Gives me an error:

1093 - Table 'vl1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

I have also tried

UPDATE list AS vl1
JOIN list AS vl2 

SET vl1.security_phrase = (
    CONCAT( SUBSTRING( vl2.phone_number FROM 1 FOR 6 ), FLOOR( RAND( ) * ( 8999 ) ) + 1000 ) 
) 
WHERE
    vl2.phone_number LIKE CONCAT( SUBSTRING( vl1.phone_number FROM 1 FOR 3 ), "%" ) 

Not working and does not give the intended results...

Any help

Answer 1

MySQL does not allow referencing the table being updated again in another subquery, unless it is inside the FROM clause (Derived Table).

Now, in your particular case, we will need to put the complete SELECT query block as a Derived Table. As discussed in chat, lead_id is your Primary Key, so we will join back using the PK to update the rows accordingly.

UPDATE list AS t1 
JOIN 
(
  SELECT
    vl1.lead_id,
    CONCAT(
        SUBSTRING(
            (
            SELECT
                vl2.phone_number 
            FROM
                list as vl2
            WHERE
                vl2.phone_number LIKE CONCAT( SUBSTRING( vl1.phone_number FROM 1 FOR 3 ), "%" ) 
            ORDER BY
                RAND( ) 
                LIMIT 1 
            ) 
        FROM
            1 FOR 6 
        ),
        FLOOR( RAND( ) * ( 8999 ) ) + 1000 
    ) AS autogenNumber 
  FROM
    list as vl1
) AS dt 
  ON dt.lead_id = t1.lead_id 
SET t1.security_phrase = dt.autogenNumber