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.