Validate duplicate records (SQL SEREVER)

Navigation
0

Very good day to all, I hope you can help me with this doubt in sql server, I have a table called PriceList with the following fields List_ID, Customer_ID, Product_ID, PriceA, PriceB which is related to my Customer and Product table.

At the time of adding a new list, you selected the client and the product and also assigned the prices, the fact is that I should be able to register several lists with the same client but with a different product, if I add a new list with the same client and a product already assigned must validate that product is already assigned to that customer.

In the same way with the other lists, I hope you can help me with the consultation in advance I thank you very much.

    
asked by OsGamez 18.10.2018 в 17:29
source

2 answers

2

You could use exists . EXISTS(o NOT EXISTS) is specially designed to verify if something exists and, therefore, it should be (and is) the best option. It will stop in the first row that matches. You should do something like this: 

 IF EXISTS (SELECT * FROM ListaPrecios WHERE Id_Producto = @Id_Producto and Id_Cliente= @Id_Cliente)
    BEGIN
    -- Aquí ejecutas el código en el caso de que exista.
    END
    ELSE
    BEGIN
    -- Aquí ejecutas el código opcional en el caso de que NO exista.
    END

Clarification: @Id_Cliente and @Id_Producto are the parameters that you must pass.

To learn more about this operator click here

I hope it helps you. Greetings!

    
answered by 18.10.2018 / 17:43
source
1

You can do this by adding a constraint in the table. But if you already have records in your table, first you must make sure that you do not have duplicates, and if you have them, delete them. 

 ALTER TABLE ListaPrecios 
 ADD CONSTRAINT clave_unica_ListaPrecios UNIQUE(Id_Cliente, Id_Producto)

I just remind you that if there are already records in your table, and there is some duplicate, you will not be able to execute the ALTER TABLE. I hope it will be useful, greetings.

    
answered by 18.10.2018 в 17:43
Group ids for the same CSS functionality Why does v-model not update the variable?