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.

Question

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.

Answer 1

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:

1. The UPDATE statement is used to update the Children table, aliasing it to c.
2. 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).
3. JOIN conditions join the users and parent tables based on email matching.
4. 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).
5. 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.