I’m somewhat of a newbie to SQL queries, especially anything containing logic, and although I've searched for hours finding the exact terms to search for is not easy in this case! I have a relatively simple one, I’m sure:
A table has 2 columns, and each row contains data about a function in a program. Some functions have a parent function associated (for grouping). Column A is the unique function ID. Column B indicates, when applicable, the parent function’s ID. All parent function IDs are independent and valid function IDs that exist elsewhere in column A.
For reporting purposes I need to list the functions grouped by their parent ID, listing the parent function with the child functions. I can easily report by parent function ID, but the problem is that a parent function does not know that it is a parent function because its column B is empty!
What I need to do is complete the value in Column B if it is empty and the function is referenced elsewhere as a parent function.
Otherwise stated, <strong>for each row</strong> that has a null value in Column B:<ol><li>Take the value from column A</li> <li>Check for the existence of that value in ANY row on column B</li> <li>If there is a match, inject the value into column B (so that Column A and B have the same value)</li> </ol>
What I have: (Query:
SELECT function_id, parent_function FROM functions)
FUNCTION_ID PARENT_FUNCTION 4 13 4 79 138 4 195 314 345 345
What I need to have:
FUNCTION_ID PARENT_FUNCTION 4 4 13 4 79 138 4 195 314 345 345 345
Any Ideas? I can't wait to get more familiar with SQL! Thanks ahead of time.Answer1:
This should work for you:
UPDATE functions SET parent_function = function_id WHERE parent_function IS NULL AND function_id IN (SELECT parent_function FROM functions)
This will set
parent_function equal to
function_id where it has not yet been set, and where it appears somewhere in the
If you don't actually want to modify the table data but still return values that you need, you can use similar logic like this:
SELECT f.function_id, COALESCE(f.parent_function, f2.function_id) as parent_function FROM functions f LEFT JOIN functions f2 ON f.function_id = f2.function_id AND f2.function_id IN (SELECT parent_function FROM functions)Answer2:
maybe you can compare the two table using EXCEPT or INTERSECT
How's this look?
select distinct t1.funx, t1.parent, case when t2.parent is null then t1.parent else t2.parent end as newparent from tbl t1 left outer join tbl t2 on t1.funx = t2.parent
<a href="http://sqlfiddle.com/#!3/55d23/15/1" rel="nofollow">sqlFiddle</a>