help with query

[rym]

Trying to figure out if I can do this with one query.

 

I have a table of stock#s and ids.  I need to be able to find any instance in a table where there are identical stock#s which have different ids.

 

For example:

stock# | id

ABC | 1001

ABC | 1002

XYZ | 1003

ABC | 1001

 

Say that is my table.  I need to be able to determine that there is a problem with all rows where the stock# is ABC because it has multple ids associated with it.  This tells me that id 1002 is actually something else and I need to give it a new stock#.  Can a single query be written which will bring back all of these instances? The table will have thousands or records.  So the query would give me a result of

ABC | 1001

ABC | 1002

Not showing any repeats. 

Then what I'm doing (not related to the query I'm looking for) is assiging one of the ids a new stock# and updating the table.  So I'd change all records with id 1002 to stock# DEF.  Then next time I get a new file it will still have the duplicates but I know about them and I update them before proceeding.  Basically my problem is I don't know about the duplicates before hand.

 

Hopefully some of this makes sense.

 

thanks

Ryan

 

 

 

 

 

 

[mjahkoh]

select * from mytable group by  stock#

thats it