1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

updation of one column based on two table values reg.

Discussion in 'SQL PL/SQL' started by laxman, Dec 14, 2009.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear SIr,
    Suppose we have a scenario like this:

    EMP TABLE DEPT TABLE
    empno dno
    ename dname
    dno
    dname

    so requirement is that i wanted to update dname column(or overwrite the dname column's values of EMP table with the dname column's values of dept table) of EMP table based on the values existing in dname column of dept table.Also if values doesn't satisfied then we need to update the dname column of EMP with 'xxxxx'.
    How i can do that? KIndly help me out.....

    Regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What has your instructor covered? I'd hate to be doing your assignments for you. Post what you have written and we can point you in the right direction; we won't do your assignment for you.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    i apologize sir, yes i am lacking some basics and that is the reason it is not striking in my mind.
    This issues are actually real time based and i have tried a lot to get the solution but couldn't.i need only some hints not the complete solution.
    Also i am not taking training anywhere ,i am completly depend on my books and to my forum only.so kindly do me this favour please.

    Regards
    Laxman
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You could have done one search in this forum itself before you posted it. There are lots of examples here.

    Anywya let us know what you tried so far , so that we can guide you accordingly..
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    OK sir, let me breif you with the scenario and what i have tried with one query:

    suppose we have one table EMP with following information:

    EMP
    eno
    ename
    dno
    dname

    and we have another table DEPT with following information
    DEPT
    dno
    dname

    Now i wanted to update dname column of the emp table with the value that is existing in dname column of dept table and also if the query is not satisfying the matching criteria i want those dname field of EMP table to set as 'xxxxx' or NULL.

    so i have tried with this query:

    update emp set dname=(select dname from dept where emp.dno=dept.dno) ;
    but also there may be few dno values of EMP table which may not be present in DEPT table ,now for this condition i want to set dname column of EMP Table to 'xxxxx'or NULL .

    Now need implement by writing a query which update the EMP table with condition which is satisfying the matching criteria as well the condition which is not satisfying the matching criteria and the query is not striking in my mind.kindly help me sir.

    Regards
    Laxman
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This is your requirement ..
    Your query already update the not matching dno with NULL. for updating it with 'xxxxx' use NVL.

    Code (SQL):

    UPDATE emp SET dname=NVL((SELECT dname FROM dept WHERE emp.dno=dept.dno),'xxxxx') ;
     
     
    laxman likes this.
  7. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thank you very much sir.but this time i promise you i will try my own at least 4-5 times then i will discuss with you with proper explanation.

    Actually i was knowing about NVL function,but failed to Analyze that i can utilise this function in this query.
    Anyway, as a beginner i treat you all senior member as a mentor and i know i will always get the solution whenever i will raise doubt.but i want to find the solution on my own and here i am finding difficulties.
    i expect some suggestion from you all guys ,how can i improve myself .let say i know very well in one topic but i am finding difficulties to transforming the specific topic's basic concept into practical level. what could be the problem?
    i am extremly sorry if i am sharing some personal view other than technical discussion.i think this is the platform where i can get few opnion with which i can improve myself.

    Thanks and regards
    Laxman
     
  8. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Laxman, nothing to worry my friend. We all were beginners once... :)
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes..

    There is no better shortcut that 'Just experiment and play around the SQL' queries. Just practice the queries when you gets the time. Experience makes the man perfect.