Updation

Question

Post reply

Updation

kapil_kk

SSC-Insane

Points: 21316
More actions
November 19, 2012 at 12:59 am

#265006

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/

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

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

BriPan SSCrazy Points: 2061 More actions · Answer 1

please post table structure and more sample data

also give more focus on what you want.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

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/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

oops sorry.....

that table is of proceduremaster only...

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

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/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

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/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

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/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 10

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

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/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

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/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 13

is there any way to handle this scenario?

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 14

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