compare data and update the table

Posted on 2014-08-20
Last Modified: 2014-08-20
Hi Team,

1. i am having 2 table to comapre the data with patient1 and patient2
fields have excatly same in both table,suppose having multiple data like 10 rows in both table

we have to check for current month only .
if exactly same patient records present ,then upd_date of patient1 table upadate with current date.

Patient1table :    patient_id   firstname  lastname upd_date
patient2table:     patient_id   firstname  lastname upd_date

plz help
Question by:nicemanish
    LVL 1

    Expert Comment

    by:Deepak Subburajan
    Its simple. If I got you correctly, here you go,

       upd_date = Getdate()
       Patient1 P1,
       Patient2 P2
       P1.patient_id = P2.patient_id

    Open in new window

    you also have mentioned as you want to do this only for the current month. Include the column in the WHERE clause. You are done.
    LVL 14

    Expert Comment

    by:Vikas Garg

    Update patient2table
    SET upd_date = GETDATE()
    WHERE Month(upd_date) = Month(Getdate())
    and patient_id  IN (Select patient_id  from Patient1table)

    Open in new window

    LVL 12

    Expert Comment

    by:Habib Pourfard
    You could write:
    UPDATE Patient1table 
    SET upd_date = GETDATE()
    FROM Patient1table P1 INNER JOIN Patient2table P2 ON P1.patient_id = P2.patient_id
    AND P1.firstname = P2.firstname  
    AND P1.lastname = P2.lastname 
    AND P1.upd_date = P2.upd_date
    WHERE DATEPART(m, P1.upd_date) = DATEPART(m, DATEADD(m, -1, getdate()))
    AND DATEPART(yyyy, P1.upd_date) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

    Open in new window


    Author Comment

    Thanks for reply
    a little bit add can we do also
    "when we have a patient record in patient2, which is not present in patient1.i want to be copy that data in patient 1 as a new record"
    LVL 12

    Accepted Solution

    INSERT INTO Patient1table (firstname, lastname, ...)
    Select P2.firstname, P2.lastname, ...
    FROM Patient1table P1 RIGHT JOIN Patient2table P2 ON P1.patient_id = P2.patient_id
    WHERE P1.patient_id IS NULL

    Open in new window


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now