Help Finding records with matching columns

I'm running MS SQL 7.0.
I've been trying to figure this out for a couple of days without success.
I need to change the values in column6 of a table to NULL where the value of
column6 is not NULL, and the value of column5 is 97, and the values of
column1, column2, column3, and column4 each match the corresponding columns
in the other row.
In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
match the same columns in row 2, and the value of column6 in row 1 is 97 and
in row2 is something else (it doesn't matter what the value is), and the
value of column 6 is not NULL.
Example:
row1 col1 col2 col3 col4 col5 col6
A B C D 97 2
row2 col1 col2 col3 col4 col5 col6
A B C D 1 3
In this example, I need to change the value in row1, column6 from 2 to NULL.
I need to do that to all rows with similar matching qualities, which I
figure to be around 1000 rows. But I DO NOT want rows returned if the value
of col6 in one of the rows is not 97.
Can anyone help?
Thanks,
John Steen
On Fri, 20 Aug 2004 08:43:01 -0700, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>I'm running MS SQL 7.0.
>I've been trying to figure this out for a couple of days without success.
>I need to change the values in column6 of a table to NULL where the value of
>column6 is not NULL, and the value of column5 is 97, and the values of
>column1, column2, column3, and column4 each match the corresponding columns
>in the other row.
>In other words, there are two rows in which columns 1, 2, 3 and 4 in row1
>match the same columns in row 2, and the value of column6 in row 1 is 97 and
>in row2 is something else (it doesn't matter what the value is), and the
>value of column 6 is not NULL.
>Example:
>row1 col1 col2 col3 col4 col5 col6
> A B C D 97 2
>row2 col1 col2 col3 col4 col5 col6
> A B C D 1 3
>In this example, I need to change the value in row1, column6 from 2 to NULL.
> I need to do that to all rows with similar matching qualities, which I
>figure to be around 1000 rows. But I DO NOT want rows returned if the value
>of col6 in one of the rows is not 97.
>Can anyone help?
>Thanks,
>John Steen
Hi John,
Pity you didn't post the DDL (CREATE TABLE statements) and INSERTS for the
sample data that would have allowed me to test. Anyway, here's an untested
suggestion:
UPDATE MyTable
SET col6 = NULL
WHERE col6 IS NOT NULL
AND col5 = 97
AND EXISTS
(SELECT *
FROM MyTable AS x
WHERE x.col1 = MyTable.col1
AND x.col2 = MyTable.col2
AND x.col3 = MyTable.col3
AND x.col4 = MyTable.col4
AND ( x.col5 <> 97
OR x.col5 IS NULL))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||if i understand your requirement correctly you can try the query given in
following example.
--sample data
create table t(col1 char(1),col2 char(1),col3 char(1),col4 char(1),col5
int,col6 int)
go
insert into t
select 'A','B','C','e',97, 2 union all --row will be updated
select 'A','B','C','e',9, 3 union all
select 'A','B','C','e',95, 2 union all
select 'A','B','C','e',97, null union all
select 'A','B','C','e',97, 5 union all --row will be updated
select 'A','B','C','x',9, 3 union all
select 'A','B','C','x',95, 2
go
--query
update t set col6 = null
where exists
(select col1,col2,col3,col4
from t x
where t.col1 = x.col1 and t.col2 = x.col2 and t.col3=x.col3 and t.col4 =
x.col4
group by col1,col2,col3,col4
having count(*) > 1)
and t.col5 = 97 and t.col6 is not null
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thanks, Hugo and Vishal! Both solutions worked.

Help designing view

Hi folks,
I've got a view, illustrated at www.dbconsult.dk/ms/sqlquestion.jpg.
There must be a smarter way of doing it, but I can't figure it out:
I wish to get rid of the CASE's.
For each row in tblFejlantal I wish for each row in tblFejlKoder to be
listet as a column.
In the illustration the alias F10/F11/etc equals tblFejlKode.FejlKode,
but with a prefix of "F".
Thanks in advance for your efforts! ;-)
Regards /SnedkerIf you can't alter the design of this database and/or you can't or simply
don't want to do this kind of transformations on the client (I'm guessing
it's for presentation purposes) maybe you might find a few pointers in this
article by Itzik Ben-Gan:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML|||On Tue, 13 Sep 2005 04:19:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
If you have suggestions to the design, I'm all ears! Anything goes.
Regards /Snedker

>If you can't alter the design of this database and/or you can't or simply
>don't want to do this kind of transformations on the client (I'm guessing
>it's for presentation purposes) maybe you might find a few pointers in this
>article by Itzik Ben-Gan:
>http://www.windowsitpro.com/Article...15608.html?Ad=1
>
>ML|||Either post the requirements regarding this specific element of your system
or post DDL and some sample data.
In any case when designing a data-centric solution follow these basic
principles:
1) identifiy all entities, starting with the essential ones;
2) identify relationships between entities; and
3) identify attributes of all entities, with special attention to those
required by genuine business needs.
ML|||On Tue, 13 Sep 2005 05:17:03 -0700, "ML"
<ML@.discussions.microsoft.com> wrote:
Thanks for your response.
As for the database design, normalizing should be okay.
Let's say we have two tables, tblPrimary and tblForeign.
tblPrimary has three columns with one row:
PrimaryID ErrValue ForeignID
1 21.7 5
tblForeign has two columns with 5 rows
ForeignID ErrorCode
1 10
2 20
3 30
4 40
5 50
My view should look like:
PrimaryID Err10 Err20 Err30 Err40 Err50
1 0 0 0 0 21.7
If a row (6, 55) is added to tblForeign the result would be
PrimaryID Err10 Err20 Err30 Err40 Err50
Err55
1 0 0 0 0 21.7 0
I want each row in tblForeign represented in my view as a column.
/Snedker

>Either post the requirements regarding this specific element of your system
>or post DDL and some sample data.
>In any case when designing a data-centric solution follow these basic
>principles:
>1) identifiy all entities, starting with the essential ones;
>2) identify relationships between entities; and
>3) identify attributes of all entities, with special attention to those
>required by genuine business needs.
>
>ML|||Based on the sample you posted I strongly believe the solution described in
Itzik's article is the way to go for you.
Now, turning it into a view is a bit trickier. One way that I can think of
(and works) is a not-really-best-practice solution involving OPENQUERY.
E.g.:
create view dbo.CrossTabbedView
as
select <column list>
from openquery(<server_name>, 'exec <procedure name> <parameters>')
As I said it might work as expected, yet it's pretty resource-intensive. On
the other hand, if you expect it to be fast, it may not "work as expected".
:)
ML

Help !

Im going mad trying and failing to figure this out . running SQl server 2000
(sp3). I have had code snippets and examples thrown at me, yet im missing
something fundermental .. if i use the following code in TSQL i get a
sucess on the executon of the DTS
Declare @.Packagename varchar(255) -- Gets most recent Version
Declare @.Userpwd Varchar(255) -- Login Password
Declare @.Intsecurity bit
Declare @.pkgpwd varchar(255)
Declare @.hr int
Declare @.Object int
Set @.Intsecurity = 0
Set @.Userpwd = 'MyPassword'
set @.pkgpwd = NULL
Set @.Packagename = 'TESTDTS'
-- Create the Package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.Object OUTPUT
If @.hr <> 0
Begin
Print 'Error Creating Package'
End
Else
Begin
Print 'Package Created'
End
-- Load the package
Declare @.svr varchar(15)
Declare @.login varchar(100)
Select @.login = 'MyUserName'
Select @.svr = @.@.serverName
Declare @.flag int
Select @.flag = 0
if @.intsecurity = 0
if @.userpwd = Null
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd
else
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd, @.ServerPassword = @.userpwd
else
begin
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword
=
@.pkgPwd
end
If @.hr <> 0
Begin
Print 'Error Loading Package'
End
Else
Begin
Print 'Package loaded'
End
EXEC @.hr = sp_OAMethod @.object, 'Execute'
If @.hr <> 0
Begin
Print 'Error Executing Package'
End
Else
Begin
Print 'Package Executed'
End
-- unitialize the package
EXEC @.hr = sp_OAMethod @.object, 'UnInitialize'
If @.hr <> 0
Begin
Print 'Error UnInitializing Package'
End
Else
Begin
Print 'Package UnInitialized'
End
-- release the package object
EXEC @.hr = sp_OADestroy @.object
If @.hr <> 0
Begin
Print 'Error Releasing Package'
End
Else
Begin
Print 'Package Released'
End
That use's SQL Authentication, however i need to use Wondows Authenticaton.
i can log on query analiser using windows Authentication, yet no matter what
i seem to do to the variables to try and get it to use Windows
Authentication it always fails. The SQL server is currently set for SQl and
Windows Authentication. Can anybody shed any light on what im overlooking ?> if @.userpwd = Null
What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
ML|||ML, this code was taken from a help page.. I have amended as you suggested
but the results are still the same . I Log onto the network using my logon
user name and password. I can open a session of T-SQL and log on usoing
windows authentication without a problem.. so i dont understand why the
loadfromsqlserver wont allow me to use windows Authentication
"ML" wrote:

> What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
>
> ML|||What about the @.Intsecurity variable? Have you tried setting it to 1 ?
ML|||ML
I have set @.Intsecurity = 1 , @.Userpwd = Null, @.login = SUSER_SNAME() and
@.Flag = 256 . still get an error -2147217843 when loading the package
"ML" wrote:

> What about the @.Intsecurity variable? Have you tried setting it to 1 ?
>
> ML|||Please post the entire error message.
Have you tried contacting the author of the script?
Is there a special reason behind executing the DTS package from T-SQL?
ML|||ML,
Im going to give up on this idea, Ive spent days at this problem with no
solution. I dont seem to be able to get any sort of error message back, onl
y
-2147217843 when i interrigate the value of @.hr. I can not find that error
number in the sql server books online. I have looked in the Server Logs ..
but nothing in there. Im a a total loss as to why this wont work with Window
s
Authentication and more frustrated at the fact that i carnt find out what is
causing the problem. I had origionaly tried to hav this run from a VB6
application that gets fired as an event from a FTP server, but then i was
still having issues on accesss rights across the network when trying to open
a txt file. I will fire a question of to the author of the code... see if he
can help at all .... if not its back to the drawing board
thanks for taking the time out to try and help
"ML" wrote:

> Please post the entire error message.
> Have you tried contacting the author of the script?
> Is there a special reason behind executing the DTS package from T-SQL?
>
> ML