Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Updation Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 12:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1386192
Posted Monday, November 19, 2012 1:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
please post table structure and more sample data
also give more focus on what you want.
Post #1386210
Posted Monday, November 19, 2012 1:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1386214
Posted Monday, November 19, 2012 2:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1386216
Posted Monday, November 19, 2012 2:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1386233
Posted Monday, November 19, 2012 2:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
oops sorry.....
that table is of proceduremaster only...



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1386244
Posted Monday, November 19, 2012 2:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1386246
Posted Monday, November 19, 2012 2:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1386247
Posted Monday, November 19, 2012 2:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1386248
Posted Monday, November 19, 2012 3:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1386254
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse