Updation

  • I have a table proceduremaster in which I need to update ParentProcedureID on basis of procedurecode and procedureID.

    create table proceduremaster

    ( procedureid int primary key ,

    procedurecode varchar(10),

    ParentProcedureID int)

    Insert into proceduremaster(procedureid, procedurecode)

    select 86, '0062'

    union all

    select 87,'0062A'

    union all

    select 88,'0062B'

    union all

    select 93,8680

    union

    select 94,8680

    Desired Result-- For all 3 rows inserted ParentProcedureID would be 86,86,86 as '0062' is parent of all procedureCode...

    i wrote the query as-

    UPDATE P

    SET P.ParentProcedureID = (SELECT TOP 1 PP.ProcedureID FROM ProcedureMaster PP

    WHERE rtrim(PP.ProcedureCode)= LEFT(RTRIM(P.ProcedureCode),LEN(RTRIM(P.ProcedureCode))-1) )

    FROM ProcedureMaster P

    WHERE right(rtrim(P.ProcedureCode),1) ='A'

    but its not working correctly.

    please suggest

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • please post table structure and more sample data

    also give more focus on what you want.

  • i have already posted the script of table creation with some sample data...

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (11/19/2012)


    i have already posted the script of table creation with some sample data...

    Well you posted the DDL for a table called ProcedureCode, but not one for ProcedureMaster, which is referenced in the update.

    If this is a true Parent Child Hierarchy, I would expect the row where ProcedureId=86 to have a NULL ParentProcedureId as this would be classed as the root node, so it cant be the parent of itself.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Heres some code that may be what you are looking for and sets the rows where the Procedurecode = '0062A' and '0062B' with 86, but doesnt do that where the Procedure Id=86.

    create table #procedurecode

    ( procedureid int primary key ,

    procedurecode varchar(10),

    ParentProcedureID int)

    Insert into #procedurecode(procedureid, procedurecode)

    select 86, '0062'

    union all

    select 87,'0062A'

    union all

    select 88,'0062B'

    Update

    pc

    Set

    pc.ParentProcedureID=p.procedureid

    from

    #procedurecode pc

    JOIN #procedurecode p on Left(pc.procedurecode,4)=P.procedurecode

    Where

    Right(pc.procedurecode,1) like '[A-Z]'

    Select * From #procedurecode

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • oops sorry.....

    that table is of proceduremaster only...

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • here is the table structure with some sample data:

    create table proceduremaster

    ( procedureid int primary key ,

    procedurecode varchar(10),

    ParentProcedureID int)

    Insert into proceduremaster(procedureid, procedurecode)

    select 86, '0062'

    union all

    select 87,'0062A'

    union all

    select 88,'0062B'

    union all

    select 93,8680

    union

    select 94,8680

    Desired o/p - for first 3 rows parentProcedureId would be 86 and for last 2 rows parentprocedureId would be 93

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • but jason I alsio need to updateed parentprocedureID of procedureId 86 as 86 only----

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (11/19/2012)


    but jason I alsio need to updateed parentprocedureID of procedureId 86 as 86 only----

    Personally I think thats the wrong path to take, as a Parent should not be the Parent of itself, its like saying that you are your own Father, it jsut doesnt make logical sense.

    however this adaption of the code will work and update the ParentprocedureId 86 with for procdure 86.

    Update

    pc

    Set

    pc.ParentProcedureID=p.procedureid

    from

    #procedurecode pc

    JOIN #procedurecode p on Left(pc.procedurecode,4)=P.procedurecode

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Hi Jason,

    Update

    pc

    Set

    pc.ParentProcedureID=p.procedureid

    from

    ProcedureMaster pc

    JOIN ProcedureMaster p on Left(pc.procedurecode,4)=P.procedurecode

    Where

    Right(pc.procedurecode,1) like '[0-9]'

    This will also works..

    your code is also working..

    thnks a lot!!!:-)

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Glad to help.

    You can actually get rid of the where Clause its superflous, or you could change it to limit the update to only rows that arnt already set or have changed.

    I suspect that if you reset the ParentprocedureId column to NULL and ran your update it wouldnt update the rows with Procedurecode '0062A' and '0062B', as A-Z arnt in the 0-9 range.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • I used 0-9 range in script for procedure that dont have alphabets in the last like- 8680,0625 etc...

    for code like '0625A', '0635B' i used the A-Z in where clause

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • i am stuck into one more case..

    if I dont have entry for procedure code '0062' and have rows for '0062A' and '0062B' then in this case how can I update parentprocedureId of '0062A' for both rows???

    plz help its urgent

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • is there any way to handle this scenario?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • There probably is but you'd have to decide the rules and which one should be considered the Anchor row.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply