I want to insert the user_Id from one table into the parent_id of another table, but there are no common values between the two tables.-PHP Chinese Network Q&A

I currently have three tables:

users table//Data is inserted during registration

usersId //PRIMARY KEY userfName userlName userUid email userPwd dateTime

parent table

id //PRIMARY KEY ikB mLname mEmail mPhone fname fLname fEmail fPhone addressL1 addressL2 city stateAbbr zip created_at user_id //FOREIGN KEY

I used the email column from the users table and mEmail or fEmail to insert the usersId into the parents table.

Children Table

child_id //PRIMARY KEY child1Name dobChild1 ageChild1 child2Name dobChild2 ageChild2 child3Name dobChild3 ageChild3 child4Name dobChild4 ageChild4 child5Name dobChild5 ageChild5 child6Name dobChild6 ageChild6 child7Name dobChild7 ageChild7 child8Name dobChild8 ageChild8 child9Name dobChild9 ageChild9 child10Name dobChild10 ageChild10 parent_id //FOREIGN KEY

There are no common values between the children's table and the other two tables. My expectation is to do a validation between the users table and the parent table, and if the users table's usersId and the parent table's user_id are equal, insert that value into the children table's parent_id. Is this possible? I considered using a cross join, but I'm concerned that performance will suffer as the number of entries in the database grows.

SELECT usersId FROM users; SELECT user_id FROM parent; UPDATE children (parent_Id) INNER JOIN parent ON children.parent_id = users.usersId UPDATE children SELECT usersId FROM users cross JOIN children.parent_id ON users.usersId = children.parent_id UPDATE children INNER JOIN users (INNER JOIN parent ON users.userId = parent.userId) ON users.usersId = parent.user_id SET children.parent_id = parent.user_Id;

I have tried every method with no success. I also tried changing the order of the connections but that didn't work either. Thank you very much for any help you can provide.

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 );

reply all (1)

P粉5961619152023-07-31 00:45:30 1 floor

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:

Explanation:

The UPDATE statement is used to update the Children table, aliasing it to c.
We use a subquery to find the user_id in the parent table that corresponds to an email in the users table (matching mEmail or fEmail).
JOIN conditions join the users and parent tables based on email matching.
The WHERE clause ensures that we only update rows in the Children table where parent_id is currently NULL (this avoids overwriting them if you have already inserted some parent IDs).
LIMIT 1 ensures we update one parent_id per child record; you can adjust this value if you need to update multiple records per child record.

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.