cmcfarland 51322 (10/8/2013)

---

Good Morning!

I'm trying to update a column based on a WHERE statement that uses a SELECT statement as the qualifier.

My goal is to find duplicate records in my table, then have a value in one of the columns change so that the users can search for and view these duplicate records and decide how they'd like to deal with them.

Here's the SELECT statement that does a nice job of finding my duplicates:

select IDX_Invoice_Number, IDX_Vendor_Number, status from _obj_3

group by IDX_Invoice_Number, IDX_Vendor_number, status

having COUNT (*)>1

Here's what I'd use to accomplish my goal, if it were possible:

update _obj_3

set status='7'

where

(

select IDX_Invoice_Number, IDX_Vendor_Number, status from _obj_3

group by IDX_Invoice_Number, IDX_Vendor_number, status

having COUNT (*)>1

)

But, I get an error because the SELECT statement yeilds multiple returns.

Which it will, because they're duplicates that it's finding.

I've tried using an INNER JOIN back to itself with no luck, and just created a VIEW using my SELECT statement, hoping to be able to update the VIEW.

No luck there, as the VIEW won't allow me to update it.

Well, thanks taking a look, and I can't wait to see what comes back.

Thanks!