SQL Locates duplicate releases and brings another table data

Asked

Viewed 51 times

-1

Good morning guys! I’m not so experienced yet in SQL commands, so I’m looking for gurus here to give me a strength.

Come on, I have a chart where they store deposit vouchers that customers send. We notice that eventually there are entries of repeated values, due to release errors. So I created a key that groups data where it is possible to identify duplicities.

SELECT Keycontrol FROM Bancocliente GROUP BY Keycontrol HAVING COUNT(*) > 1;

The problem is that I have two fields with the same description and if you give an update both will suffer cancellation. To solve the problem, I need the return of this select to be within another select that will bring the largest ID of that result, so I will know that I am excluding the second release of the client.

I need to bring the Code of the same table. To later do an UPDATE using the code.

TABLE FIELDS BANK Code
Keychain

sql
  • 2

    Do your repeated control keys refer to the same customer code or different customer codes? Anyway search here on Sopt for deletion of duplicate SQL records.

2 answers

0

My boss friend, very grateful!! I gave it right and I managed to get the information I needed.

The final command was like this:

SELECT MAX(Código) as ID, ChaveControle
FROM BancoCliente
WHERE RIGHT(ChaveControle,1)<>"D"
GROUP BY ChaveControle
HAVING COUNT(*) > 1

Now what I need to do is take this result and make a UPDATE modifying the STATUS for CANCELED. Do you think you can?

chieftain

-1

Assuming that the table has, in addition to the fields it indicates, a field that uniquely identifies each input, which example pair we call ID.

With the expression below, we get only one result for each duplicate Keycontrol.

SELECT * 
FROM BancoCliente
WHERE (ID, ChaveControle) IN (
    SELECT MAX(ID), ChaveControle
    FROM BancoCliente
    GROUP BY ChaveControle
    HAVING COUNT(*) > 1
)

The MAX criterion can be changed by any other (MIN, FIRST, LAST) that returns one of the existing values.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.