1 2 3 Previous Next 40 Replies Latest reply on Jul 30, 2013 9:59 PM by rp0428

    Oracle Program/Query

    969952

      Hi ,

       

      I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.

      Now I would like to insert values into this EMP table using below conditions.

      Insert into EMP(user_seq.nextval,

                              (select empno from employees where empno in(....(empnumbers),

                              (select org_id from organizations where  org_name='XXXXXXXXXX'),

                              (select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') ))

       

      Please provide provide me the query for the above requirement.

       

      Thanks.

        • 1. Re: Oracle Program/Query
          Hoek

          Just join the tables?

           

          Insert into EMP

          select user_seq.nextval

          ,      e.empno

          ,      o.org_id

          ,      d.dept_id

          from   employees e

          ,      organizations o

          ,      dept t

          where  <joins go here>

          and    <rest of predicates>

          • 2. Re: Oracle Program/Query
            Greg Spall

            tell you what ..

            forget the INSERT .. seriously.

             

            Write a single SQL statement that returns what you want to see in the target table:

             

            Perhaps?

             

            select user_seq.nextval, e.empno, o.org_id, d.deptid
            from employeees e, organizations o, dept d
            where e.dept_id = d.dept_id
            and e.org_id = o.org_id;
            

             

             

            if you can do that - (and thereby showing us how your tables are related).

            We'll happily show you how to insert that data into that table

             

            As it stands, however, we have no idea what your tables look like, how they related, nothing ... so we can't help.

            • 3. Re: Oracle Program/Query
              Greg Spall

              Hoek wrote:

               

              Just join the tables?

               

              Withcraft! I say!!

              Burn the witch!

               

              • 4. Re: Oracle Program/Query
                969952

                we can write joins but we are mapping the records and retrieving only that particular records.. so how can I retrieve obly that particular records?

                • 5. Re: Oracle Program/Query
                  Hoek

                  I still don't see the problem:

                  insert into emp

                  select user_seq.nextval

                  ,      e.empno

                  ,      o.org_id

                  ,      d.dept_id

                  from   employees e

                  ,      organizations o

                  ,      dept t

                  where  <joins go here>

                  and    e.empno in (empnumbers)

                  and    o.org_name = 'XXXXXXXXXX')

                  and    d.dname in ('MANAGER','ANALYST','SALESMAN');

                   

                  Think in sets and joins, databases are born to join and crunch datasets...

                  • 6. Re: Oracle Program/Query
                    Frank Kulash

                    Hi,

                     

                    969952 wrote:

                     

                    we can write joins but we are mapping the records and retrieving only that particular records.. so how can I retrieve obly that particular records?

                    Like so many other things, that depends on your data and your requirements.  Unless you say where you're coming from and where you want to go, you can't expect anyone to give you very good directions.

                     

                    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

                    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

                    If you're asking about a DML statement, such as INSERT, the CREATE TABLE and INSERT statementsyou post should re-create the tables as they are before the DML, and the results  will be the contents of the changed table(s) when everything is finished.

                    Always say which version of Oracle you're using (for example, 11.2.0.2.0).

                    See the forum FAQ: https://forums.oracle.com/message/9362002

                    • 7. Re: Oracle Program/Query
                      969952

                      I would like to write a query as follows in teh same block

                       

                      update groups set group_id= (select div_id from div where div_code in('ABC','DEF','GHI','KLMNS'));

                       

                      pelase suggest me

                      • 8. Re: Oracle Program/Query
                        Greg Spall

                        Create a small sample test case.

                        Fake data, fake tables (similar enough to your own so you can relate).

                         

                        Once that's done, again, I'll go back to my request to you: Forget updates, and inserts. Write a single SQL that returns the end result that you want.

                        Once you have that, stuffing it into a table (via INSERT or UPDATE is trivial).

                        And what you'll usually find - is generating that initial SQL is not very hard as well

                        • 9. Re: Oracle Program/Query
                          969952

                          Please correct the following code..

                           

                          begin

                               for i in (select div_id from div, groups where DIV.DIV_CODE=groups.group_name)

                              loop

                              update groups set group_id=i.div_id;

                              end loop;

                              end;

                          • 10. Re: Oracle Program/Query
                            Frank Kulash

                            Hi,

                             

                             

                             

                            969952 wrote:

                             

                            Please correct the following code..

                             

                            begin

                                 for i in (select div_id from div, groups where DIV.DIV_CODE=groups.group_name)

                                loop

                                update groups set group_id=i.div_id;

                                end loop;

                                end;

                            No kidding: without knowing what you want to do, it's very hard to say how to do it.  Post some sample data (CREATE TABLE and INSERT statements for all tables involved, as they are before the UPDATE) and the results you want from that data (i. e., the contents of  than changed table after the UPDATE).

                             

                            The code above is updating every row in the groups table over and over again.  Every time, it sets groupd_id to the same value for all rows.  Since there is no order to the query that governs the loop, it is arbitrary which one will be done last, which is the value that all rows will have.  I'll bet that's not what you want, but as long as I don't know what you do want, I can't say how to do it.

                            • 11. Re: Oracle Program/Query
                              969952

                              getting error with following update statement :

                               

                              update groups set group_id=( select d.div_id,g.group_id from div d, groups g where d.DIV_CODE=g.group_name);

                               

                              too many rows error returing... need to keep it in loop please help me out

                              • 12. Re: Oracle Program/Query
                                969952

                                It is not updating... am getting ORA-00001: unique constraint (Schema.GROUPS_ID_PK) violated..

                                • 13. Re: Oracle Program/Query
                                  Greg Spall

                                  We're back to:

                                   

                                  "Please set up a simple example test case" so we can help.

                                  Without something we can run at our end, we can't help you ..

                                  Did you read the link Frank posted?

                                   

                                  You want us to take the time to solve your problem, but you don't want to take the time to post a complete question?

                                  Yeah, seems fair

                                  • 14. Re: Oracle Program/Query
                                    SomeoneElse

                                    Apparently we're supposed to divine this information.

                                     

                                    Isn't that what an Oracle originally was?

                                    1 2 3 Previous Next