SQL Unknown P2 usage on Query

user_12054184 0
Viewed 54

I have a SQL query below:

INSERT INTO `permissions` (
  `name`,
  `display_name`,
  `module_id`,
  `parent_id`,
  `parent_name`,
  `is_main_menu`,
  `order`,
  `is_active`
)
VALUES
  (
    'appointment_type.index',
    "msg('lbl_appointment_type')",
    23,
    (SELECT
      p2.id
    FROM
      (SELECT
        *
      FROM
        permissions
      WHERE NAME = "setting.mentor_mentee") p2),
    "setting.mentor_mentee",
    0,
    1,
    1
  );

I am having trouble to understand portion below:

(SELECT p2.id FROM (SELECT * FROM permissions WHERE NAME = "setting.mentor_mentee")p2)

I am not sure what I am looking into. What is this p2? Please point me to right direction. Thanks in advance.

mysql sql
1 Answers

It is doing nothing whatsoever. You can phrase this as:

VALUES ('appointment_type.index', 'msg(''lbl_appointment_type'')', 23,
        (SELECT p.id
         FROM permissions p
         WHERE p.NAME = 'setting.mentor_mentee'
        ),
        'setting.mentor_mentee', 0, 1, 1
      )

Note the preference for single quotes.

In practice, I would recommend doing this using insert . . . select:

INSERT INTO
    SELECT 'appointment_type.index', 'msg('lbl_appointment_type')', 23,
            p.id
            'setting.mentor_mentee', 0, 1, 1
    FROM permissions p
    WHERE NAME = 'setting.mentor_mentee';

Why did someone write the code with the subquery? Presumably, the person who wrote the query confused INSERT with UPDATE/DELETE. Those do not allow subqueries to reference the table being modified -- except using a hack like double subqueries. The normal solution is not a double subquery but using JOIN.

This is not an issue with INSERT because conceptually the subquery returns its result set before the rows are inserted.

user_11440350
Related