CODEWITHSUNDEEP
mysqlsql
Pilling Fine
Pilling Fine

Reputation: 147

Select parent row cell value for child row in MySQL

I am importing data from an Excel sheet, and I am at a point where I have one table with parent and child rows. Child rows have around the same columns as the parent rows, except that the child row has null in column A, which is my primary key for the parent rows and my foreign key-to-be for the child row.

Sample data

 - AI ID | Employee Number | Employee Name
 - 1     | 33400           | John
 - 2     | NULL            | John's Wife
 - 3     | NULL            | John's Child
 - 4     | 43443           | Susan
 - 5     | NULL            | Susan's Husband

How can I fill the rows 2,3,4 Employee Number that have NULL with the values of their parent rows.

So that Employee Number column becomes like this

 - 33400
 - 33400
 - 33400
 - 43443
 - 43443

Note: Update This is a real world scenario, so no field of the parent row is necessary identifying it's child rows. It's unfortunate the table above implies employee name to be doing so.

What is actually identifying child-parent row dependency is the AI ID column. Each child row belongs to the first row above it with employer number NOT NULL when all rows are ordered by AI ID. So ideally I was am looking at a query that does the following:

IF Employee_Number IS NULL
SELECT Employee_Number OF FIRST ROW
WHERE Employee_Number IS NOT NULL AND ROW_ABOVE.AI_ID < CURRENT_ROW.AI_ID

Something of the sorts above.

Upvotes: 3

Views: 2330

Answers (4)

Pilling Fine
Pilling Fine

Reputation: 147

Demo Here: http://rextester.com/FDJYO55558

Have done the following, but I do not imply it to be efficient (resource wise) in any way since it uses a sub query. So for my purpose of a once off import exercise, this is just bare bones of what achieves what I want. If more efficient ways can come, I'ld be more than willing to learn!

CREATE TABLE yourTable (`AI_ID` int, `Employee_Number` int, `Employee_Name` varchar(255));
INSERT INTO yourTable (`AI_ID`, `Employee_Number`, `Employee_Name`)
VALUES
(1, 33400, 'John'),
(2, NULL,  'John''s Wife'),
(3, NULL,  'John''s Child'),
(4, 43443, 'Susan'),
(5, NULL,  'Susan''s Husband');

SELECT *, 
(SELECT Employee_Number FROM yourTable innerTable 
    WHERE innerTable.AI_ID <= outerTable.AI_ID
    AND innerTable.Employee_Number IS NOT NULL
    ORDER BY innerTable.AI_ID DESC
    LIMIT 1) AS Parent_Employee_Number FROM yourTable outerTable

Upvotes: 0

ProgrammerBoy
ProgrammerBoy

Reputation: 891

I would suggest to do more research before you do this table update. It a usual case that we can have employees with the same names. In that case you will end up updating records having NULL employee number with incorrect Employee Number.

After your research, write a script with few sql statements to do the purpose than firing a single update. Don't miss taking backup of your table.

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a query like the following:

UPDATE Table1 AS t1
JOIN Table1 AS t2 ON t1.EmployeeName LIKE CONCAT(t2.EmployeeName, '%')
SET t1.EmployeeNumber = t2.EmployeeNumber
WHERE t1.EmployeeNumber IS NULL

The query works as long as there is exactly one matching parent record for each child record based on the first part of EmployeeName.

Demo here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

SELECT t1.`AI ID`,
       t2.`Employee Number`,
       t1.`Employee Name`
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.`Employee Name` LIKE CONCAT('%', t2.`Employee Name`, '%')
WHERE t2.`Employee Number` IS NOT NULL;

Output:

enter image description here

Demo here:

Rextester

Late edit:

Seeing the answer by @Giorgos made me wonder whether you want to actually update your table. If so, my query can be slightly modified to:

UPDATE yourTable t1
INNER JOIN yourTable t2
    ON t1.`Employee Name` LIKE CONCAT('%', t2.`Employee Name`, '%')
SET t1.`Employee Number` = t2.`Employee Number`
WHERE t2.`Employee Number` IS NOT NULL;

Upvotes: 2

Related Questions