sql server codes: update

Showing posts with label update. Show all posts

Friday, March 30, 2012

Help on using CASE together with UPDATE

Hi
I need some help on how to update some fields with a value based on the
value in another field. I have tried to do this with a CASE statement, but I
haven't really been able to get anywhere near something that works...
If I run the select statement -
"select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
a.zipcode, z.cityname, a.title from user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' ",
then it gives the records I want to update. What I then want to do, is to
update the field u.userdepartment with a value based on the value of the
field "title". Eg. when the field title has the value "IT" then I'd like to
set userdepartment = 2959028.
I'd think that I can use something like ...userdepartment = CASE title = 'IT' then 2959028... but apparently I need some guidedance on how to do
this.
Can any of you help with this?
Regards
Steenuntested code follows:
Does this select statement return what you are looking for?
SELECT user, title,
userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
If so, this might be the update statement that you are looking for:
UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
--
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Try Something on these lines:
UPDATE user
SET userdepartment = CASE title
WHEN 'IT' THEN 2959028
WHEN 'HR' THEN 2959029
ELSE NULL
END
From address a
INNER JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' and u.addressidentold = a.addressident
-- Note: code not tested ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Hi
Thanks to both of you - by "combining" your examples I got it working.
Keith - the second line of your example should be
...userdepartment = CASE title When 'IT' then 29... then it works...
It's always a joy to use this newsgroup - no matter what stupid and simple
question being asked, there're always a lot of helpfull answers to us less
"sql-skilled" people......
Thanks
Steen
.
Keith Kratochvil wrote:
> untested code follows:
> Does this select statement return what you are looking for?
> SELECT user, title,
> userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
> If so, this might be the update statement that you are looking for:
> UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I need some help on how to update some fields with a value based on
>> the value in another field. I have tried to do this with a CASE
>> statement, but I haven't really been able to get anywhere near
>> something that works...
>> If I run the select statement -
>> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
>> a.zipcode, z.cityname, a.title from user u
>> JOIN address a on u.addressidentold = a.addressident
>> JOIN Zipcode z ON a.zipcode=z.zipcode
>> where u.userinit='spe' ",
>> then it gives the records I want to update. What I then want to do,
>> is to update the field u.userdepartment with a value based on the
>> value of the field "title". Eg. when the field title has the value
>> "IT" then I'd like to set userdepartment = 2959028.
>> I'd think that I can use something like ...userdepartment = CASE
>> title = 'IT' then 2959028... but apparently I need some guidedance
>> on how to do this.
>> Can any of you help with this?
>> Regards
>> Steen|||There are two ways to do CASE. This is the other method. My revised
example should work correctly:
CASE WHEN title = 'IT' THEN 2959028
WHEN title = 'MARKETING' THEN 1
WHEN title = '...' THEN 2
ELSE NULL END
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Of9LDvGrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks to both of you - by "combining" your examples I got it working.
> Keith - the second line of your example should be
> ...userdepartment = CASE title When 'IT' then 29... then it works...
> It's always a joy to use this newsgroup - no matter what stupid and simple
> question being asked, there're always a lot of helpfull answers to us less
> "sql-skilled" people......
> Thanks
> Steen
> .
> Keith Kratochvil wrote:
> > untested code follows:
> >
> > Does this select statement return what you are looking for?
> >
> > SELECT user, title,
> > userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> > If so, this might be the update statement that you are looking for:
> > UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I need some help on how to update some fields with a value based on
> >> the value in another field. I have tried to do this with a CASE
> >> statement, but I haven't really been able to get anywhere near
> >> something that works...
> >>
> >> If I run the select statement -
> >>
> >> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> >> a.zipcode, z.cityname, a.title from user u
> >> JOIN address a on u.addressidentold = a.addressident
> >> JOIN Zipcode z ON a.zipcode=z.zipcode
> >> where u.userinit='spe' ",
> >>
> >> then it gives the records I want to update. What I then want to do,
> >> is to update the field u.userdepartment with a value based on the
> >> value of the field "title". Eg. when the field title has the value
> >> "IT" then I'd like to set userdepartment = 2959028.
> >> I'd think that I can use something like ...userdepartment = CASE
> >> title = 'IT' then 2959028... but apparently I need some guidedance
> >> on how to do this.
> >>
> >> Can any of you help with this?
> >>
> >> Regards
> >> Steen
>

Help on using CASE together with UPDATE

Help on UPDATE with EXIST

I have records in seven tables that must be inserted/updated in another table. The table structures are exactally alike. The seven tables contain Aircraft position data by time. The single table is the Radars table and should contain Aircraft data by time for each cooresponding entry in each of the seven tables.

Here is a storeProcedure that I wrote to insert data from one of the seven tables if an entry does not already exist in the Radars table:

Insert Into dbo.Radars
Select * From dbo.CG70
Where [Time] = @.eTime and
(not exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above code works fine in the insertion of a record into the Radars when one does not already exist. The following code should update the entire row in the Radars table when a TRACKNUM is found and the record should be replaces with the corresponding one from the CG70 table for a new Time value.

Update dbo.Radars
Set TRACKNUM = TRACKNUM
Select * From dbo.CG70
Where [Time] = @.eTime and
(exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above UPDATE does not work and I'm not currently smart enough to figure out why. Can someone point me in the proper direction? Please don't write my code for me, just tell me where I'm wrong.

thanks.

Quote:

Originally Posted by joecousins

Here is a storeProcedure that I wrote to insert data from one of the seven tables if an entry does not already exist in the Radars table:

Insert Into dbo.Radars
Select * From dbo.CG70
Where [Time] = @.eTime and
(not exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

Update dbo.Radars
Set TRACKNUM = TRACKNUM
Select * From dbo.CG70
Where [Time] = @.eTime and
(exists( select dbo.Radars.TRACKNUM from dbo.Radars where dbo.Radars.TRACKNUM = dbo.CG70.TRACKNUM));

The above UPDATE does not work and I'm not currently smart enough to figure out why. Can someone point me in the proper direction? Please don't write my code for me, just tell me where I'm wrong.

thanks.

Update query should look like

Update [TableName]
SET [ColumnName] = [Value]
WHERE [Condition]

In your query, You have a select statement in between SET and WHERE Clause, So after SET, you Select will return some values and your WHERE clause is not being used for Update.

Also Check you SET value. It should refer to the Column you want to Update with, not to itself.sql

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 on this sp, it should be PERFECT!

Hi there, could someone tell me what is the wrong with the below SP's CURSOR.I exec this sp through a table when update a filed value like this :
EXECUTE THROUGH TRIGGER
exec SP_ALOCATE_PAT_CREDIT @.patid, @.creditno, @.totalamount, @.userid, @.sdesc ,@.creDate,'C'

When i run execute this sp, I got this error :
ERROR:
Another user has modified the contents of this table or view;the database row you are modifying no longer exists in the database.Databse error: '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'INVOICE_LIST' does not exist.[Microsoft][ODBC SQL Server driver][SQL Server]The statement has terminated.'

==============================
THANX GUYZ, ALREADY SOLVED IT FINALLY:beer: , ON TODAY EARLY MORNING.WELL, I GUES U GUYS R RITE, I NEED MORE PRACTISE BUT I DID GOOD AT THIS STAGE TOO EVENTOUGH IT IS NOT SO STANDARD..HAHA ,THNX ANYWAY, WILL WORK TO BE BETTER! :angel:Wow. That is horrible.
If you want this to be perfect, you have a helluva long way to go. As a matter of fact, you would be best off scrapping absolutely everything you have done so far and starting again from the beginning. Your whole design is conceptually flawed.
You are using cursors unnecessarily, and you are calling this procedure from a trigger without any reference to what records it should act upon, and no handling for multi-record inserts either.
First, rewrite your procedure using set-based operations instead of a cursor.
Then, read the sections on triggers in Books Online until you understand the purpose and utility of the INSERTED and DELETED virtual tables.|||Thanx for ur concern BLINDMAN, As for my level, i beleive this is what I can output as a totaly new to sql world.i need this to be set up as soon as posible.With minimum guide and help i gues this is what i can come so far.So i hope a guide or few examples would be greatly helpful rather thehn ur advise.thanx Mr BLINMAN eventhough u werent help me much|||Sorry tommy boy - you might not like it much but blindman is right. Apart from it being a flawed idea in the first place the execution is all wrong too. As a self confessed newby you would be well advised to read his post and think seriously about where to go from here. There are no hints, tips or tricks to sort it out - you need a wholesale redesign.|||Oh Than Poo*, Then I shall consider it. God bless america ;)|||God bless them indeed.|||The truth hurts sometimes, TommyBoy, but the truth is what I gave you and nothing but the truth.
Better you know now that you have been heading down the wrong path, and that this is going to take some time and effort for you to implement.
On the plus side, if you take the time to explain WHAT you are trying to do, and you are willing to listen to people on this forum, then we can give you some good advice on designing and coding your process. Its OK to be a noob. We don't mind noobs on the forum. We do have little patience with people who want free advice and then insist on doing things wrong. That is just a waste of our time.
So post a new thread desribing your situation and ask for some help on engineering the process.

Wednesday, March 28, 2012

help on SQL UPDATE needed

UPDATE tbl_person SET tbl_person.col_state=2 WHERE
lnk_company_person.col_id_person=tbl_person.col_id_person and
lnk_company_person.col_id_company=3
The error returned by the above UPDATE was:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'lnk_company_person' does not match with a table name or
alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'lnk_company_person' does not match with a table name or
alias name used in the query.
Lnk_company_person is an existing table. Should I use sub-select? How? I
haven't used SQL for some time. Would appreciate any help.
Thanks,
BingYou need to have a FROM clause..
UPDATE tbl_person
SET col_state = 2
FROM tbl_person T, lnk_company_person U
WHERE U.col_id_person = T.col_id_person and
U.col_id_company = 3|||You haven't included the lnk_company_person as a table in the update
statement. The form you need is something like:
UPDATE tbl_person SET col_state=2
WHERE exists (select * from lnk_company_person as lnk
where lnk.col_id_person=tbl_person.col_id_person and
lnk.col_id_company=3)
or
UPDATE tbl_person SET col_state=2
FROM lnk_company_person as lnk
WHERE lnk.col_id_person=tbl_person.col_id_person and
lnk.col_id_company=3
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4AE68D95-E04E-455A-AFF3-83A8F295B0D0@.microsoft.com...
> UPDATE tbl_person SET tbl_person.col_state=2 WHERE
> lnk_company_person.col_id_person=tbl_person.col_id_person and
> lnk_company_person.col_id_company=3
> The error returned by the above UPDATE was:
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'lnk_company_person' does not match with a table name or
> alias name used in the query.
> Server: Msg 107, Level 16, State 1, Line 1
> The column prefix 'lnk_company_person' does not match with a table name or
> alias name used in the query.
> Lnk_company_person is an existing table. Should I use sub-select? How?
I
> haven't used SQL for some time. Would appreciate any help.
> Thanks,
> Bing|||On Tue, 4 Jan 2005 13:57:06 -0800, bing wrote:
>UPDATE tbl_person SET tbl_person.col_state=2 WHERE
>lnk_company_person.col_id_person=tbl_person.col_id_person and
>lnk_company_person.col_id_company=3
>The error returned by the above UPDATE was:
>Server: Msg 107, Level 16, State 3, Line 1
>The column prefix 'lnk_company_person' does not match with a table name or
>alias name used in the query.
>Server: Msg 107, Level 16, State 1, Line 1
>The column prefix 'lnk_company_person' does not match with a table name or
>alias name used in the query.
>Lnk_company_person is an existing table. Should I use sub-select? How? I
>haven't used SQL for some time. Would appreciate any help.
Hi Bing,
It's hard to guess what you want, but try if the following works for you:
UPDATE tbl_person
SET col_state = 2
WHERE EXISTS (SELECT *
FROM lnk_company_person AS l
WHERE l.col_id_person = tbl_person.col_id_person
AND l.col_id_company = 3)
If this doesn't achieve what you want, then you'll need to post more
details, as described here: http://www.aspfaq.com/5006.
Also, please remove the prefixes from your table and column names! Are you
calling yourself person_Bing?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks all who replied. I did not know FROM could be used in UPDATE. The
online book does not show FROM in UPDATE's syntax. Anyway, good to learn
something new.
Bing
"Scott Morris" wrote:
> You haven't included the lnk_company_person as a table in the update
> statement. The form you need is something like:
> UPDATE tbl_person SET col_state=2
> WHERE exists (select * from lnk_company_person as lnk
> where lnk.col_id_person=tbl_person.col_id_person and
> lnk.col_id_company=3)
> or
> UPDATE tbl_person SET col_state=2
> FROM lnk_company_person as lnk
> WHERE lnk.col_id_person=tbl_person.col_id_person and
> lnk.col_id_company=3
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:4AE68D95-E04E-455A-AFF3-83A8F295B0D0@.microsoft.com...
> >
> > UPDATE tbl_person SET tbl_person.col_state=2 WHERE
> > lnk_company_person.col_id_person=tbl_person.col_id_person and
> > lnk_company_person.col_id_company=3
> >
> > The error returned by the above UPDATE was:
> >
> > Server: Msg 107, Level 16, State 3, Line 1
> > The column prefix 'lnk_company_person' does not match with a table name or
> > alias name used in the query.
> > Server: Msg 107, Level 16, State 1, Line 1
> > The column prefix 'lnk_company_person' does not match with a table name or
> > alias name used in the query.
> >
> > Lnk_company_person is an existing table. Should I use sub-select? How?
> I
> > haven't used SQL for some time. Would appreciate any help.
> >
> > Thanks,
> >
> > Bing
>
>|||>The online book does not show FROM in UPDATE's syntax.
Really? Which topic are you looking at? The UPDATE topic under
Transact-SQL Reference shows the following syntax:
Syntax
UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @.variable = expression
| @.variable = column = expression } [ ,...n ]
{ { [ FROM { < table_source > } [ ,...n ] ]
[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,...n ] ) ]
If you're not seeing this in the UPDATE topic, please let me know.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"bing" <bing@.discussions.microsoft.com> wrote in message
news:88C20751-F1B5-4194-9958-74ADABC18C91@.microsoft.com...
> Thanks all who replied. I did not know FROM could be used in UPDATE. The
> online book does not show FROM in UPDATE's syntax. Anyway, good to learn
> something new.
> Bing
> "Scott Morris" wrote:
>> You haven't included the lnk_company_person as a table in the update
>> statement. The form you need is something like:
>> UPDATE tbl_person SET col_state=2
>> WHERE exists (select * from lnk_company_person as lnk
>> where lnk.col_id_person=tbl_person.col_id_person and
>> lnk.col_id_company=3)
>> or
>> UPDATE tbl_person SET col_state=2
>> FROM lnk_company_person as lnk
>> WHERE lnk.col_id_person=tbl_person.col_id_person and
>> lnk.col_id_company=3
>>
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:4AE68D95-E04E-455A-AFF3-83A8F295B0D0@.microsoft.com...
>> >
>> > UPDATE tbl_person SET tbl_person.col_state=2 WHERE
>> > lnk_company_person.col_id_person=tbl_person.col_id_person and
>> > lnk_company_person.col_id_company=3
>> >
>> > The error returned by the above UPDATE was:
>> >
>> > Server: Msg 107, Level 16, State 3, Line 1
>> > The column prefix 'lnk_company_person' does not match with a table name
>> > or
>> > alias name used in the query.
>> > Server: Msg 107, Level 16, State 1, Line 1
>> > The column prefix 'lnk_company_person' does not match with a table name
>> > or
>> > alias name used in the query.
>> >
>> > Lnk_company_person is an existing table. Should I use sub-select?
>> > How?
>> I
>> > haven't used SQL for some time. Would appreciate any help.
>> >
>> > Thanks,
>> >
>> > Bing
>>|||Oh, hmmm...The topic I looked at was 'Embedded SQL for C and SQL Server'.
Syntax
UPDATE {table_name | view_name} SET {column=expression[,...]} WHERE CURRENT
OF cursor_name
Why is this UPDATE syntax different from the one in Transact-SQL Reference?
Bing
"Gail Erickson [MS]" wrote:
> >The online book does not show FROM in UPDATE's syntax.
> Really? Which topic are you looking at? The UPDATE topic under
> Transact-SQL Reference shows the following syntax:
> Syntax
> UPDATE
> {
> table_name WITH ( < table_hint_limited > [ ...n ] )
> | view_name
> | rowset_function_limited
> }
> SET
> { column_name = { expression | DEFAULT | NULL }
> | @.variable = expression
> | @.variable = column = expression } [ ,...n ]
> { { [ FROM { < table_source > } [ ,...n ] ]
> [ WHERE
> < search_condition > ] }
> |
> [ WHERE CURRENT OF
> { { [ GLOBAL ] cursor_name } | cursor_variable_name }
> ] }
> [ OPTION ( < query_hint > [ ,...n ] ) ]
> If you're not seeing this in the UPDATE topic, please let me know.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:88C20751-F1B5-4194-9958-74ADABC18C91@.microsoft.com...
> > Thanks all who replied. I did not know FROM could be used in UPDATE. The
> > online book does not show FROM in UPDATE's syntax. Anyway, good to learn
> > something new.
> >
> > Bing
> >
> > "Scott Morris" wrote:
> >
> >> You haven't included the lnk_company_person as a table in the update
> >> statement. The form you need is something like:
> >>
> >> UPDATE tbl_person SET col_state=2
> >> WHERE exists (select * from lnk_company_person as lnk
> >> where lnk.col_id_person=tbl_person.col_id_person and
> >> lnk.col_id_company=3)
> >>
> >> or
> >>
> >> UPDATE tbl_person SET col_state=2
> >> FROM lnk_company_person as lnk
> >> WHERE lnk.col_id_person=tbl_person.col_id_person and
> >> lnk.col_id_company=3
> >>
> >>
> >> "bing" <bing@.discussions.microsoft.com> wrote in message
> >> news:4AE68D95-E04E-455A-AFF3-83A8F295B0D0@.microsoft.com...
> >> >
> >> > UPDATE tbl_person SET tbl_person.col_state=2 WHERE
> >> > lnk_company_person.col_id_person=tbl_person.col_id_person and
> >> > lnk_company_person.col_id_company=3
> >> >
> >> > The error returned by the above UPDATE was:
> >> >
> >> > Server: Msg 107, Level 16, State 3, Line 1
> >> > The column prefix 'lnk_company_person' does not match with a table name
> >> > or
> >> > alias name used in the query.
> >> > Server: Msg 107, Level 16, State 1, Line 1
> >> > The column prefix 'lnk_company_person' does not match with a table name
> >> > or
> >> > alias name used in the query.
> >> >
> >> > Lnk_company_person is an existing table. Should I use sub-select?
> >> > How?
> >> I
> >> > haven't used SQL for some time. Would appreciate any help.
> >> >
> >> > Thanks,
> >> >
> >> > Bing
> >>
> >>
> >>
>
>|||"Hugo Kornelis" wrote:
> Hi Bing,
> It's hard to guess what you want, but try if the following works for you:
> UPDATE tbl_person
> SET col_state = 2
> WHERE EXISTS (SELECT *
> FROM lnk_company_person AS l
> WHERE l.col_id_person = tbl_person.col_id_person
> AND l.col_id_company = 3)
>
Thanks, Hugo. I believe it should do what I want.
> Also, please remove the prefixes from your table and column names! Are you
> calling yourself person_Bing?
>
Hmm...I don't get this part. Which prefix should not be used? That's how
the tables and columns are defined by the vendor's application. Please
advise.
Bing|||On Wed, 5 Jan 2005 06:39:10 -0800, bing wrote:
(snip)
>> Also, please remove the prefixes from your table and column names! Are you
>> calling yourself person_Bing?
>Hmm...I don't get this part. Which prefix should not be used? That's how
>the tables and columns are defined by the vendor's application. Please
>advise.
Hi Bing,
If you're stuck with the table and column names as they are, you're stuck
with them. But if you have the power to change them, then do so.
It is generally considered bad practice to prefix table names with tbl_
and column names with col_ (or, another common prefix, some short form of
the data type, like int_). Some googling should bring up many discussions
about this.
The reason why prefixing your table name with tbl_ is bad, is that most
parts of SQL consider tables to be equivalent to views. This means that
you could replace your Customers table with a CustomersNew table, then
create a view named Customers that converts the CustomerNew data to the
"old" format. If you do that, most of your code will simply keep working
without having to be changed.
You can do the same if the table is called tbl_Customers, but then you'll
end up with a *view* called tbl_Customers. This takes the prefix ad
ridiculum. And if you relabel the view as vw_Customers, you'll still have
to go over and change all your current code.
The same goes for column prefixes indicating the datatype (doesn't apply
to your case, but I'm on my soapbox now <g>). I've seen numerous examples
of columns named int_Size or float_Amount. What will happen if some change
in the business requires you to make Size a bigint? Or a decimal (15,2)?
Will you go over all your code, changing each occurence of int_Size to
dec_Size or bigint_Size? Will you use global search and replace (with the
risk of an unwanted change in the wrong place)? Who'll retest all the code
affected by this simple change?
As to the prefix col_, the risk of change is not there - but it just seems
silly to type col_ in front of each column name. Just as it would be silly
if we all addressed all other people with "Person (name)" (e.g. "Person
Bing", "Person Hugo", "Person Scott" and "Person Gail").
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo, good to know. Thanks much for your lucid explanation. Right now, I'm
unfortunately pretty much stuck with the vendor.
Bing
"Hugo Kornelis" wrote:
> On Wed, 5 Jan 2005 06:39:10 -0800, bing wrote:
> (snip)
> >> Also, please remove the prefixes from your table and column names! Are you
> >> calling yourself person_Bing?
> >>
> >
> >Hmm...I don't get this part. Which prefix should not be used? That's how
> >the tables and columns are defined by the vendor's application. Please
> >advise.
> Hi Bing,
> If you're stuck with the table and column names as they are, you're stuck
> with them. But if you have the power to change them, then do so.
> It is generally considered bad practice to prefix table names with tbl_
> and column names with col_ (or, another common prefix, some short form of
> the data type, like int_). Some googling should bring up many discussions
> about this.
> The reason why prefixing your table name with tbl_ is bad, is that most
> parts of SQL consider tables to be equivalent to views. This means that
> you could replace your Customers table with a CustomersNew table, then
> create a view named Customers that converts the CustomerNew data to the
> "old" format. If you do that, most of your code will simply keep working
> without having to be changed.
> You can do the same if the table is called tbl_Customers, but then you'll
> end up with a *view* called tbl_Customers. This takes the prefix ad
> ridiculum. And if you relabel the view as vw_Customers, you'll still have
> to go over and change all your current code.
> The same goes for column prefixes indicating the datatype (doesn't apply
> to your case, but I'm on my soapbox now <g>). I've seen numerous examples
> of columns named int_Size or float_Amount. What will happen if some change
> in the business requires you to make Size a bigint? Or a decimal (15,2)?
> Will you go over all your code, changing each occurence of int_Size to
> dec_Size or bigint_Size? Will you use global search and replace (with the
> risk of an unwanted change in the wrong place)? Who'll retest all the code
> affected by this simple change?
> As to the prefix col_, the risk of change is not there - but it just seems
> silly to type col_ in front of each column name. Just as it would be silly
> if we all addressed all other people with "Person (name)" (e.g. "Person
> Bing", "Person Hugo", "Person Scott" and "Person Gail").
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||I disagree the argument by Hugo!.. Sorry, but its always good practice
to prefix tables, stored proces, views, triggers, indexes with
appropriate prefixes (and sometime with suffix as well)... I agree its
not a good practice to prefix the column names with the data type.. But
most of the time, these tables, stored procs, views and triggers are
never going to be replaced by anything else. Its always easy to read
and also following naming standards will make any developer understand
the objects more quickly...! There are a ton of threads on this topic
on google and other newsgroups...|||> Oh, hmmm...The topic I looked at was 'Embedded SQL for C and SQL Server'.
> Why is this UPDATE syntax different from the one in Transact-SQL
> Reference?
My guess is that the topic was not updated. The whole Embedded SQL for C
API was deprecated quite some time ago. (It's not supported at all in SQL
Server 2005). As such, I doubt that the writers spent any significant time
in those topics.
At any rate, I assume you found that topic in error and you're not trying
to use the Embedded SQL for C funcationality. I know that finding the
correct topics in BOL can be a bit of a challenge (we're working on making
it easier). It's always good to check the blue bar at the top of the topic
to make sure you're in the right area. For T-SQL statements, you really
want to be in the Transact-SQL Reference area.
Hope that helps,
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"bing" <bing@.discussions.microsoft.com> wrote in message
news:8111C214-8648-425C-9ECF-D3BFE4DF7C86@.microsoft.com...
> Oh, hmmm...The topic I looked at was 'Embedded SQL for C and SQL Server'.
> Syntax
> UPDATE {table_name | view_name} SET {column=expression[,...]} WHERE
> CURRENT
> OF cursor_name
> Why is this UPDATE syntax different from the one in Transact-SQL
> Reference?
> Bing
> "Gail Erickson [MS]" wrote:
>> >The online book does not show FROM in UPDATE's syntax.
>> Really? Which topic are you looking at? The UPDATE topic under
>> Transact-SQL Reference shows the following syntax:
>> Syntax
>> UPDATE
>> {
>> table_name WITH ( < table_hint_limited > [ ...n ] )
>> | view_name
>> | rowset_function_limited
>> }
>> SET
>> { column_name = { expression | DEFAULT | NULL }
>> | @.variable = expression
>> | @.variable = column = expression } [ ,...n ]
>> { { [ FROM { < table_source > } [ ,...n ] ]
>> [ WHERE
>> < search_condition > ] }
>> |
>> [ WHERE CURRENT OF
>> { { [ GLOBAL ] cursor_name } | cursor_variable_name }
>> ] }
>> [ OPTION ( < query_hint > [ ,...n ] ) ]
>> If you're not seeing this in the UPDATE topic, please let me know.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:88C20751-F1B5-4194-9958-74ADABC18C91@.microsoft.com...
>> > Thanks all who replied. I did not know FROM could be used in UPDATE.
>> > The
>> > online book does not show FROM in UPDATE's syntax. Anyway, good to
>> > learn
>> > something new.
>> >
>> > Bing
>> >
>> > "Scott Morris" wrote:
>> >
>> >> You haven't included the lnk_company_person as a table in the update
>> >> statement. The form you need is something like:
>> >>
>> >> UPDATE tbl_person SET col_state=2
>> >> WHERE exists (select * from lnk_company_person as lnk
>> >> where lnk.col_id_person=tbl_person.col_id_person and
>> >> lnk.col_id_company=3)
>> >>
>> >> or
>> >>
>> >> UPDATE tbl_person SET col_state=2
>> >> FROM lnk_company_person as lnk
>> >> WHERE lnk.col_id_person=tbl_person.col_id_person and
>> >> lnk.col_id_company=3
>> >>
>> >>
>> >> "bing" <bing@.discussions.microsoft.com> wrote in message
>> >> news:4AE68D95-E04E-455A-AFF3-83A8F295B0D0@.microsoft.com...
>> >> >
>> >> > UPDATE tbl_person SET tbl_person.col_state=2 WHERE
>> >> > lnk_company_person.col_id_person=tbl_person.col_id_person and
>> >> > lnk_company_person.col_id_company=3
>> >> >
>> >> > The error returned by the above UPDATE was:
>> >> >
>> >> > Server: Msg 107, Level 16, State 3, Line 1
>> >> > The column prefix 'lnk_company_person' does not match with a table
>> >> > name
>> >> > or
>> >> > alias name used in the query.
>> >> > Server: Msg 107, Level 16, State 1, Line 1
>> >> > The column prefix 'lnk_company_person' does not match with a table
>> >> > name
>> >> > or
>> >> > alias name used in the query.
>> >> >
>> >> > Lnk_company_person is an existing table. Should I use sub-select?
>> >> > How?
>> >> I
>> >> > haven't used SQL for some time. Would appreciate any help.
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Bing
>> >>
>> >>
>> >>
>>|||On 5 Jan 2005 10:21:04 -0800, SQLDBA wrote:
>I disagree the argument by Hugo!.. Sorry, but its always good practice
>to prefix tables, stored proces, views, triggers, indexes with
>appropriate prefixes (and sometime with suffix as well)... I agree its
>not a good practice to prefix the column names with the data type.. But
>most of the time, these tables, stored procs, views and triggers are
>never going to be replaced by anything else. Its always easy to read
>and also following naming standards will make any developer understand
>the objects more quickly...! There are a ton of threads on this topic
>on google and other newsgroups...
Hi SQLDBA,
You might wish to consult ISO-11179, the international standard for naming
conventions.
Unfortunately, this standard is not available for free. Joe Celko often
posts a good summary of the most important aspects. Google for "Celko" +
"ISO-11179" and you should be able to find it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 30, 2012

Help on using CASE together with UPDATE

Help on using CASE together with UPDATE

Help on UPDATE with EXIST

Help on update statement

Help on update statement

Help on this sp, it should be PERFECT!

Wednesday, March 28, 2012

help on SQL UPDATE needed

help on SQL UPDATE needed

help on SQL UPDATE needed

sql server codes

Blog Archive

About Me