Updating ID

  • 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/

  • hey, I am done with this....

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

  • This was removed by the editor as SPAM

  • 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/

  • 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