locked
checking duplicate rows in tables w/o PK RRS feed

  • Question

  • User1312587436 posted

    I have 5 tables as follows

    as it can be seen from diagram, I have one table ie.patientdetails with Pid as primary key

    and other 4 tables have pid which is foreign key related to Pid in patient details.

    the problem is that when if a test data is filled for say patient with Pid as 1, the data is saved nicely in required tables

    If i try doing that again for same patient then a duplicate is created in test tables with pid as 1.

    How to prevent that?

    Please help

    Saturday, March 12, 2011 8:17 AM

Answers

  • User1288251396 posted
    DECLARE @Count INT
    SELECT @Count = COUNT(*) FROM MyTable WHERE Pid = @Pid
    IF @Count = 0 THEN
    BEGIN
        INSERT MyTable ...
    END
    ELSE
    BEGIN
        UPDATE MyTable SET <whatever> WHERE Pid = @Pid
    END
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 12, 2011 12:39 PM
  • User1288251396 posted

    but will this work for a pid that is autogenerated ????

    because in my case the pid for patientdetails table ` is autogenerated .

    That shouldn't matter, as long as you know what the pid is.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 12, 2011 2:17 PM

All replies

  • User1288251396 posted

    If I understand you correctly, you're trying to keep a one-to-one relationship between the patient and test tables.  To do this, I'd suggest you create a stored procedure that checks for an existing record.  If it finds one, it should do an UPDATE, and if it doesn't, it can do an INSERT.

    Saturday, March 12, 2011 12:09 PM
  • User1312587436 posted

    you're trying to keep a one-to-one relationship between the patient and test tables

    Yes, exactly.

    create a stored procedure that checks for an existing record.  If it finds one, it should do an UPDATE, and if it doesn't, it can do an INSERT.

    can you please provide a sample code for it.

    please if you can.

    thank you.

    Saturday, March 12, 2011 12:22 PM
  • User1288251396 posted
    DECLARE @Count INT
    SELECT @Count = COUNT(*) FROM MyTable WHERE Pid = @Pid
    IF @Count = 0 THEN
    BEGIN
        INSERT MyTable ...
    END
    ELSE
    BEGIN
        UPDATE MyTable SET <whatever> WHERE Pid = @Pid
    END
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 12, 2011 12:39 PM
  • User1312587436 posted

    Thanks a lot!

    Saturday, March 12, 2011 1:01 PM
  • User1312587436 posted

    but will this work for a pid that is autogenerated ????

    because in my case the pid for patientdetails table ` is autogenerated .

    Saturday, March 12, 2011 2:12 PM
  • User1288251396 posted

    but will this work for a pid that is autogenerated ????

    because in my case the pid for patientdetails table ` is autogenerated .

    That shouldn't matter, as long as you know what the pid is.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 12, 2011 2:17 PM
  • User1312587436 posted

    Got it working!

    Thanks Again!

    Saturday, March 12, 2011 2:53 PM