Click here to Skip to main content
15,072,748 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How can i prevent getting duplicate data when calling this SP? So my table have two exactly same data but i would wanted to only display one

Everytime i call this SP, Duplicate data will display out which mean i have two A data in my table & both of the data would be display but i just want display 1 A data

SO what i want is How can i display the data just only once even thought have multiple duplicate in the table

What I have tried:

my SP

SQL
PROCEDURE tt.Get_myDATA(
 IN copy_running_no VARCHAR(50)
)
BEGIN
  SELECT 
         
         unit,
         car_qty,
         car_unit_price,
         car_value,
  FROM   sum_form_1
  WHERE  running_no= copy_running_no
  AND NOT EXISTS (SELECT 1 FROM calculate_form WHERE running_no = prm_running_no )

UNION ALL
  SELECT unit,
         car_qty,
         car_unit_price,
         car_value,
  FROM   calculate_form 
  WHERE  running_no = prm_running_no ;

END
Posted
Updated 26-Nov-20 22:18pm
v3

1 solution

Quote:
How can i display the data just only once even thought have multiple duplicate in the table
You could use a DISTINCT[^] clause for a single query
But in this case it is more likely to be your use of UNION ALL. Use just UNION instead and SQL will get rid of the duplicates for you

Edit:
For completeness sake I feel I should point out a couple of ways of avoiding duplicates in the first place..

You could query for the data first and only insert if not found - this is known as "upsert" and there are three examples of how to that at MySQL UPSERT | Three Techniques to Perform an UPSERT[^]
There is more information about using a unique index to prevent duplicates at Using MySQL UNIQUE Index To Prevent Duplicates[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900