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 needed on select and insert query

a bit of a begginer's request here.
I have a table called t_user in which username is the primary key and one of
the fields is a decimal called bsl.
I'm trying to write a stored procedure that selects the right record based
on the username (input from a cookie) and then inserts the bsl value based o
n
the users input into a textbox on the asp.net page
the stored proc code is
CREATE PROCEDURE addbsl
@.bsl decimal,
@.username varchar(20)
AS
SELECT username FROM t_user
WHERE username = @.username
insert into t_user (bsl) values (@.bsl)
GO
and when i run it in the query analyser it tells me:
Server: Msg 515, Level 16, State 2, Procedure addbsl, Line 8
Cannot insert the value NULL into column 'username', table
'FYProj.dbo.t_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Stored Procedure: FYProj.dbo.addbsl
Return Code = -6
I understand that it is trying to insert a username as well but cannot
because you cannot enter a null value but I don't want it to input a sername
i just want it to insert data into the record with the specified username
Is there a way to change the sproc to get it to work or am i just taking the
wrong approach?You will have to use an UPDATE statement to update an existing row. INSERT
always insert a new row.
Hello neil_pat,

> a bit of a begginer's request here.
<snip>
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B