Re: Help Finding records with matching columns

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/20/04

  • Next message: Vishal Parkar: "Re: Help Finding records with matching columns" 
    Date: Sat, 21 Aug 2004 00:29:21 +0200

    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)
  • Next message: Vishal Parkar: "Re: Help Finding records with matching columns"