Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

T-SQL syntax question... 1

Status
Not open for further replies.

Sheffield

Programmer
Joined
Jun 1, 2001
Messages
180
Location
US
Greetings,

Can anyone please briefly explain a method by which I can Update all duplicated values in a field to 'Null'?

Although I can clearly distinguish the fields in question, I don't know how to refer to them in T-SQL.

Many thanks in advance:-)
 

Please explain with a little more detail and perhaps an example what you need to do. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
I have a particular field that has several values that may be duplicates. I need to make Null any occurrance after the first.

The following are only three of the table's fields as they are now:
Code:
  ID   NAME         DOLLARS
  --   -----------  ------
  01   Joe          25
  02   Joe          25
  03   Joe          25
  04   Steve        30
  05   Steve        30

Whereas, I need them to look like this...
Code:
  ID   NAME         DOLLARS
  --   -----------  ------
  01   Joe          25
  02   Joe          Null
  03   Joe          Null
  04   Steve        30
  05   Steve        Null

I'm assuming that my query will look something like this...
Code:
SELECT a.ID, a.Name,
   CASE
      WHEN (a.Dollars) = (SELECT MAX(DISTINCT a.Dollars)
                          FROM TableA a
                          WHERE a.Name = b.Name)
      THEN (a.Dollars)
      ELSE Null
      END AS "Max Dollars"
FROM TableA a INNER JOIN TableB b on a.Name = b.Name)
ORDER BY a.ID, a.Name, a."Max Dollars" ASC

...However, this code doesn't catch duplicates, but rather Nulls out values less than the MAX.

 

Do you want to actually update the table or only select a result set with the desired result?

Does the table have a unique key such as the ID field?

Is the data actually grouped as in your example? Are the following configurations possible.
[tt]
ID NAME DOLLARS
-- ----------- ------
01 Joe 25
02 Joe 25
03 Joe 25

04 Steve 30
05 Steve 30
06 Joe 25
07 Joe 25
08 Joe 25

[/tt]
In this case, would the records 06 thru 08 be considered together with 01 thru 03 or as a separate group?
[tt]
ID NAME DOLLARS
-- ----------- ------
01 Joe 25
02 Steve 30
03 Steve 30

04 Joe 25
05 Joe 25
06 Steve 30

[/tt]
Similar to the last question - Would all records for Steve and all records for Joe be considered together?
[tt]
ID NAME DOLLARS
-- ----------- ------
01 Joe 25
02 Joe 50
03 Joe 25
04 Steve 30
05 Steve 32
06 Steve 30
[/tt]

Would the records be considered duplicates if there are differing values between duplicates? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Many thanks for your response:-)

To answer your questions:

1. I wish to only select a result set with the desired result...NOT update the table.
2. The table does have a unique key, which is the ID field.
3. With regards to grouping, the following situation is indeed possible:
Code:
  ID   NAME         DOLLARS
  --   -----------  ------
Code:
  01   Joe          25
  02   Joe          25
  03   Joe          25
Code:
  04   Steve        30
  05   Steve        30
Code:
  06   Joe          25
  07   Joe          25
  08   Joe          25

Therefore, records 06 thru 08 are to be considered together with 01 thru 03 as the same group.

The below situation is also possible.
Code:
  ID   NAME         DOLLARS
  --   -----------  ------
Code:
  01   Joe          25
Code:
  02   Steve        30
  03   Steve        30
Code:
  04   Joe          25
  05   Joe          25
Code:
  06   Steve        30

4. No. All records for Steve and all records for Joe would not be considered together.

This following situation is NOT possible because Dollars amounts are always the same with regards to each name.
Code:
  ID   NAME         DOLLARS
  --   -----------  ------
  01   Joe          25
  02   Joe
Code:
          50
Code:
 should be 25
  03   Joe          25
  04   Steve        30
  05   Steve
Code:
        32
Code:
 should be 30 
  06   Steve        30

5. Records are only considered duplicates when the 'Dollars' field is repeated for a name (like Joe) two or more times. For Example:
Code:
  ID   NAME         DOLLARS
  --   -----------  ------
  01   Joe          25
  02   Joe          25

Again....many, many thanks for your help with this:-)
 

Thanks for helping me understand and answering my questions so thoroughly. The following query does what you want.

Select
a.ID, a.Nm,
Case
When a.ID=b.MinID Then a.Dollars
Else Null End As Dollars
From TableA As a
Left Join
(Select Min(ID) As MinID, Nm, Dollars
From TableA
Group By Name, Dollars) As b
On a.Name=b.Name And a.Dollars=b.Dollars
Order By a.Name, a.ID
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top