P粉5961619152023-07-31 00:45:30
According to your description, you want to update the parent_id column in the child table based on the matching value between the user table and the parent table. You want to update the parent_id in the child table with the usersId from the users table and the user_id from the parent table.
You can do this using the following SQL query:
UPDATE Children c SET c.parent_id = ( SELECT p.user_id FROM users u JOIN parent p ON u.email = p.mEmail OR u.email = p.fEmail WHERE c.parent_id IS NULL -- Only update rows with NULL parent_id to avoid overwriting existing values LIMIT 1 -- Assuming you want to update one parent_id per child record; you may modify this as needed );
Explanation:
Be sure to back up your database before running any update queries, just to be safe. ,
As for your concerns about performance as database entries grow, this query should be efficient as long as there are appropriate indexes on the relevant columns, such as email in the users table and mEmail and fEmail in the parent table . Indexes will significantly speed up the lookup process, especially when working with large data sets.