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)
>
>.
>

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,
DeeDee,
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
>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
>
>.
>

Help me create a SQL Statement

I have a database that is far from being rational! I need to query the tables to get a hierarchy resultset.
Here is the structure:
levelOne
levelOne_A
levelTwo
levelThree
levelFour
my problem is that table levelOne_A and table levelTwo have data that are considered to fall underneath levelOne. However the data is far different from each other so I can not able merge both into one.
so the it should look like this:
levelOne
levelOne_A AND levelTwo
levelThree
levelFour

Any ideas how I can get my hierarchy structure?
Thanks for your help!

I am not following you at all. Are levelOne, levelOne_A, etc.,tables in your database? Are they columns in a table? Arethey pieces of data? Could you explain further? Can youprovide some data examples? And could you use a non-spaced font(like courier) to make sure the hierachy you need is accuratelyrepresented?