Menu
Is there a way of avoid duplicate rows only when status = 'S'? Example:
I tried to create an index "unique" but when status = 'N' it should allow duplicate, then it did not work correctly.
Any sugestions to avoid duplicates in SQL?
514
Set a field's Indexed property to Yes (No duplicates) Select the field that you want to make sure has unique values. In the Field Properties pane at the bottom of the table design view, on the General tab, set the Indexed property to Yes (No duplicates). Save the changes to your table.
If you want the query to return only unique rows, use the keyword DISTINCT after SELECT . DISTINCT can be used to fetch unique rows from one or more columns. You need to list the columns after the DISTINCT keyword.
The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique.
Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
Yes, you could use filtered index:
CREATE UNIQUE INDEX UIX_name ON tab(fk_client) WHERE status = 'S';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
