CODEWITHSUNDEEP
mysqlsql
Pol
Pol

Reputation: 25535

UPDATE FROM SELECT with foreign key on parent with one query

How to update (change from first select table value second) second_table.first_table_id if first_table.email match in both select.

If it even possible. With one query!

----------------------------------------- UPDATE -----------------------------------------

EXAMPLE:

I need to update foreign key of second table if email field match in first table. I need to compare two query results with different parent_id (parents are in in same table with children)

         table_1
 -------------------------
| id |  parent_id | email |
 -------------------------
  1     NULL       NULL      
  2     NULL       NULL
  3      1         [email protected]
  4      2         [email protected]
  5      1         [email protected]
  6      2         [email protected]

     table_2
 ----------------
| id |  first_id |
 ----------------
   1       3
   2       4
   3       5
   4       6

  1. I have two parents with ids 1 and 2 and some children (ids: 3,4,5,6).

  2. Also, keep in mind: 1 - old, 2 - new

  3. Task: change foreign key in second table if children email with parent_id = 1 and chilren email with parent_id = 2 match (are the same).

  4. In our example in second table row with id = 3 its foreign key field - first_id has to change from 5 to 4.

Upvotes: 0

Views: 403

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

Following might get you started

UPDATE  Table_2 t2u
SET     first_id = (
  SELECT  t2.first_id
  FROM    Table_2 t2
          INNER JOIN Table_1 t1 ON t1.id = t2.first_id
          INNER JOIN (
            SELECT  parent_id = MAX(parent_id), email
            FROM    Table_1
            GROUP BY
                    email
          ) t1p ON t1p.email = t1.email
          INNER JOIN Table_1 t1i ON   t1i.email = t1p.email
                                      AND t1i.parent_id = t1p.parent_id
  WHERE   t2u.first_id <> t1i.id)

Test script (SQL Server)

;WITH Table_1 (id, parent_id, email) AS (
  SELECT 1, NULL, NULL      
  UNION ALL SELECT 2, NULL, NULL
  UNION ALL SELECT 3, 1, '[email protected]'
  UNION ALL SELECT 4, 2, '[email protected]'
  UNION ALL SELECT 5, 1, '[email protected]'
  UNION ALL SELECT 6, 2, '[email protected]'
)
, Table_2 (id, first_id) AS (
    SELECT 1, 3
    UNION ALL SELECT 2, 4
    UNION ALL SELECT 3, 5
    UNION ALL SELECT 4, 6
)
SELECT  t2.*, t1i.id as [update with]
FROM    Table_2 t2
        INNER JOIN Table_1 t1 ON t1.id = t2.first_id
        INNER JOIN (
          SELECT  parent_id = MAX(parent_id), email
          FROM    Table_1
          GROUP BY
                  email
        ) t1p ON t1p.email = t1.email
        INNER JOIN Table_1 t1i ON   t1i.email = t1p.email
                                    AND t1i.parent_id = t1p.parent_id
WHERE   t2.first_id <> t1i.id

Output

id          first_id    update with 
----------- ----------- -----------
3           5           4

Upvotes: 1

Related Questions