I don't understand this: "TABLE_A has only 1 record for each logical_name, TABLE_B can have multiple."
Table_b can have multiple records for each logical_name? Is there some kind of relationship between "logical_name" and "relationship_name"? As for thought process, I tend to think in terms of similarities. For example, "this SQL problem is like another one that I encountered". So, there are patterns, or templates. If your goal is to improve your ability to write creative SQL solutions, then I would suggest looking at many examples of working SQL. The more SQL queries you examine and work through, the more you will see patterns of use.. Regards, Mike On Sun, Sep 27, 2009 at 7:12 PM, JJ <sa...@temporaryinbox.com> wrote: > > I wanted to ask about what methodology do you SQL gurus use to build > queries? How do you go about in your thinking process? > > I know how to build simple queries but I often get stuck on what the > proper approach is on the more complex ones. > > For example I have this situation where I need to update a table based > on conditions that are partially from that table and partially from a > related table. > > Would you split something like this into subqueries and start from the > bottom-most subquery or something like that? > > > A simplified example of these tables are: > > TABLE_A FIELDS: > logical_name > type > lifecycle_status > > > TABLE_B FIELDS: > relationship_name > type > active > > > TABLE_A has only 1 record for each logical_name, TABLE_B can have > multiple. > > I need to set the "active" field in TABLE_B to "false" for records > that meet the following criteria: > > TABLE_A.LOGICAL_NAME = 'DEVICE_1' > OR TABLE_A.LOGICAL_NAME = 'DEVICE_2' > OR TABLE_A.LOGICAL_NAME = 'DEVICE_3' > > AND TABLE_A.TYPE = 'database' > AND CIRELATIONSHIPM1.BC_TYPE = 'SERVICE-DATABASE' > AND CIRELATIONSHIPM1.BC_ACTIVE = 't' > > AND TABLE_A.BC_LIFECYCLE_STATUS = 'RETIRED' > OR COUNT( CIRELATIONSHIPM1.RELATIONSHIP_NAME ) > 1) > > > > Thanks in advance, > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
