Help on update statement

We have the table called Organization. In this table is a
column called OutlineNum that indicates the hierarchy of
the organizations. This table also includes a flag to
indicate whether or not the organization is active. A
user can inactivate an organization, but ONLY when its
child(ren) are inactivated also. Since our apps code did
not do this at the time, we now may have bad data out
there.
Can anyone out there help me out on creating an update
statement to fix the data based on the OutlineNum and
ActiveFlag values? Below is the DDL for the Organization
table.
create table Organization
(
OrganizationID nvarchar(15) not null,
OrganizationName nvarchar(30) not null,
ActiveFlag int not null
default 1
constraint CK_Organization_ActiveFlag check
(ActiveFlag in (1,0)),
OutlineNum nvarchar(60) not null,
constraint PK_Organization primary key (OrganizationID)
)
go
Below is some sample data. You will have to tweak it to
put bad data in.
insert into Organization
values ('ORG1000', 'Organization - ORG1000',1,'1')
insert into Organization
values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
insert into Organization
values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
insert into Organization
values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
insert into Organization
values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
insert into Organization
values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
insert into Organization
values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
insert into Organization
values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
insert into Organization
values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
insert into Organization
values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
insert into Organization
values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
insert into Organization
values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
insert into Organization
values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
insert into Organization
values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
Thanks in advance,
Dee
Dee,
I just spent a few mins but don't have a concrete solution right now.
Just FYI I created 2 SQLs to get my feet wet into the direction I was going.
I'm sure you will figure out what I'm doing with these SQLs.
I'm at work right now and don't want to spend any further time on this.
Will try and do it from home for ya.
Rgds,
Harman Sahni
select m.outlinenum as moutlinenum,
c.outlinenum as coutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where m.outlinenum = substring(c.outlinenum,1,len(m.outlinenum))
order by 1,2
select c.outlinenum as coutlinenum,
m.outlinenum as moutlinenum,
c.activeflag as cactiveflag
from organization m,
organization c
where substring(c.outlinenum,1,len(c.outlinenum)-2) = m.outlinenum and
len(c.outlinenum) > 2
order by len(c.outlinenum), len(m.outlinenum), 1,2
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee
|||Oh by the way, in my SQLs
c is for children
m is for master
"dee" <anonymous@.discussions.microsoft.com> wrote in message
news:9bf801c49757$031a9840$a601280a@.phx.gbl...
> We have the table called Organization. In this table is a
> column called OutlineNum that indicates the hierarchy of
> the organizations. This table also includes a flag to
> indicate whether or not the organization is active. A
> user can inactivate an organization, but ONLY when its
> child(ren) are inactivated also. Since our apps code did
> not do this at the time, we now may have bad data out
> there.
> Can anyone out there help me out on creating an update
> statement to fix the data based on the OutlineNum and
> ActiveFlag values? Below is the DDL for the Organization
> table.
> create table Organization
> (
> OrganizationID nvarchar(15) not null,
> OrganizationName nvarchar(30) not null,
> ActiveFlag int not null
> default 1
> constraint CK_Organization_ActiveFlag check
> (ActiveFlag in (1,0)),
> OutlineNum nvarchar(60) not null,
> constraint PK_Organization primary key (OrganizationID)
> )
> go
> Below is some sample data. You will have to tweak it to
> put bad data in.
> insert into Organization
> values ('ORG1000', 'Organization - ORG1000',1,'1')
> insert into Organization
> values ('ORG1002', 'Organization - ORG1002', 1, '1.1')
> insert into Organization
> values ('ORG1003', 'Organization - ORG1003', 1, '1.2')
> insert into Organization
> values ('ORG1004', 'Organization - ORG1004', 1, '1.1.2')
> insert into Organization
> values ('ORG1005', 'Organization - ORG1005', 1, '1.1.3')
> insert into Organization
> values ('ORG1006', 'Organization - ORG1006', 1, '1.1.4')
> insert into Organization
> values ('ORG1007', 'Organization - ORG1007', 1, '1.2.1')
> insert into Organization
> values ('ORG1008', 'Organization - ORG1008', 1, '1.2.2')
> insert into Organization
> values ('ORG1009', 'Organization - ORG1009', 1, '1.2.3')
> insert into Organization
> values ('ORG1011', 'Organization - ORG1011', 1, '1.1.5')
> insert into Organization
> values ('ORG1012', 'Organization - ORG1012', 1, '1.2.4')
> insert into Organization
> values ('ORG1013', 'Organization - ORG1013', 1, '1.3')
> insert into Organization
> values ('ORG1014', 'Organization - ORG1014', 1, '1.2.5')
> insert into Organization
> values ('ORG1015', 'Organization - ORG1015', 1, '1.1.1')
> Thanks in advance,
> Dee
|||Hello Dee
Not knowing finer details of what you are wanting to achieve, I have put
togather a basic cursor that may achieve this for you. Be warned that
running the UPDATE statement(s) outside of a transaction may end up
updating data that is not intended to be updated. I would recommend for you
to back the database up before running any of the commands from the script
below.
--BEGIN TRAN
declare @.active char(1)
declare @.inactive char(1)
declare @.outline varchar(20)
set @.active = 1 --define the value for which you would like to delete
set @.inactive = 0
declare c1 cursor for
select distinct outlinenum
from organization
where activeflag = @.active and len(outlinenum) = 3 --assuming that parent
that was mistakenly updated has length of 3 eg. 1.1 is considered the
parent.
open c1
fetch next from c1 into @.outline
while @.@.fetch_status = 0
begin
print 'update organization set outlinenum = ' + '''' + @.inactive + '''' +
'where outline like ' + '''' + @.outline + '%' + '''' --check the statements
to see if this meets the goals of what you want to do
--update organization set outlinenum = @.inactive where outline like
@.outline + '%'
fetch next from c1 into @.outline
end
close c1
deallocate c1
--COMMIT TRAN
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thank you both Harman and Pankaj! You both definitely
pointed me to the right direction. Both of your ideas
worked like a charm.

>--Original Message--
>Dee,
>I just spent a few mins but don't have a concrete
solution right now.
>Just FYI I created 2 SQLs to get my feet wet into the
direction I was going.
>I'm sure you will figure out what I'm doing with these
SQLs.
>I'm at work right now and don't want to spend any further
time on this.
>Will try and do it from home for ya.
>Rgds,
>Harman Sahni
>
>select m.outlinenum as moutlinenum,
> c.outlinenum as coutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where m.outlinenum = substring(c.outlinenum,1,len
(m.outlinenum))
>order by 1,2
>select c.outlinenum as coutlinenum,
> m.outlinenum as moutlinenum,
> c.activeflag as cactiveflag
>from organization m,
> organization c
>where substring(c.outlinenum,1,len(c.outlinenum)-2) =
m.outlinenum and
>len(c.outlinenum) > 2
>order by len(c.outlinenum), len(m.outlinenum), 1,2
>
>"dee" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:9bf801c49757$031a9840$a601280a@.phx.gbl...
is a[vbcol=seagreen]
did[vbcol=seagreen]
Organization[vbcol=seagreen]
null,[vbcol=seagreen]
null,[vbcol=seagreen]
null[vbcol=seagreen]
null,[vbcol=seagreen]
(OrganizationID)
>
>.
>