‘Connect By’ can be used in SQL to read/update hierarchical data in a table.
It has the following syntax:
[ Start With (condition1) ] Connect By [ NoCycle ] (condition2)
An example for condition2 above can be:
Connect by prior employee_id = manager_id and
prior manager_id = director_id
To see the effect of ‘prior’ keyword, let us just reverse the application of ‘prior’ from emp_id to mgr_id
So, prior just helps to determine which row is treated as parent by the DB.
Example to show usage of ‘Start With’:
‘Connect by’ can be made to work on multiple tables too (in case parent child relationship is split across 2 tables) by first joining the 2 tables and then running the connect by.
|Email:||(Your email is not shared with anybody)|