Post reply

Updating ID

  • November 23, 2012 at 5:40 am

    #267175

    I have a table PatientLocDtl in which I need to update AccountIDon basis of ChartNo and PatientID.

    create table PatientLocDtl

    (Account int ,

    ChartNo varchar(10),

    PatientID int)

    Insert into PatientLocDtl (PatientId,chartNo)

    select 8, '1-1'

    union all

    select 9,'1-2'

    union all

    select 10,'1-3'

    union all

    select 11,'6-1'

    union all

    select 12,'6-2'

    Desired Result-- For all 3 rows whose chartno starting with 1 should insert AccountID be 8 as '1-1' is parent of all ChartNo and associated with PatientId 8..similarly for chartno starting with 6 accountid would be 11

    i wrote the query as-

    select

    (select top 1 a.PatientID

    from PatientLocDtl a

    where Right(a.Chart_No,1) like '%[0-9]' and a.PatientID = b.PatientID

    )as AccountID

    ,b.PatientID,b.chart_no

    from PatientLocDtl b order by b.PatientID asc

    but its not working correctly.

    please suggest

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

  • November 23, 2012 at 6:06 am

    #1561851

    hey, I am done with this....

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

  • November 23, 2012 at 6:13 am

    This was removed by the editor as SPAM

  • November 23, 2012 at 6:24 am

    #1561855

    I create this one and it works- :-):-)

    update PC

    set pc.account =p.patientID

    from PatientLocDtl pc

    join patientlocdtl p on left(p.ChartNo,len(SUBSTRING(p.ChartNo,PATINDEX('%-%',''),len(p.ChartNo))))=left(pc.ChartNo,len(SUBSTRING(pc.ChartNo,PATINDEX('%-%',''),len(pc.ChartNo))))

    where right(p.chartno,1) like '[0-9]'

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

  • November 23, 2012 at 6:26 am

    #1561857

    how to post query in this format?

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

Viewing 5 posts - 1 through 4 (of 4 total)

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