Sql updating a table from itself

You can update a table by referencing information in other tables.List these other tables in the FROM clause or use a subquery as part of the WHERE condition. If you need to include the target table of the UPDATE statement in the list, use an alias. The condition can be a simple predicate on a column or a condition based on the result of a subquery.I considered not having a ROOT_ID, but I'm not sure how I would be able to retrieve the entire tree if I only had PARENT_ID. CREATE TABLE UNIQUE_PHYSICIAN ( PARENT_PHYS_ID NUMBER, PHYS_ID NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY); ALTER TABLE DPISYS. PARENT_PHYS_ID you should use a surrogate key, preferable generated from a sequence.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS. In this case there is no reason (and no possibility) to change them ever. The point of what I'm doing is that by querying a root_id, I can get all of the results in that tree, because everything in the tree refers to both the parent and the root.The root's root and parent IDs are its own unique ID.

sql updating a table from itself-55sql updating a table from itself-88

I want the ROOT_ID to propagate down the tree I've built.

For example, a table and a trigger can have the same name (however, to avoid confusion, this is not recommended).

A trigger is fired based on a triggering statement, which specifies: statement might include a list of columns.

Edit: How this works is that each record has a unique ID, a parent ID, and a root ID.

I basically have a tree, each member of that tree has a root_ID pointing at the unique ID of the root and a parent ID pointing at the one above it.