[Last Call] Learn how to a build a cloud-first strategyRegister Now


compare data and update the table

Posted on 2014-08-20
Medium Priority
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

Expert Comment

by:Deepak Subburajan
ID: 40272209
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 15

Expert Comment

by:Vikas Garg
ID: 40272212

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
ID: 40272213
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.


Author Comment

ID: 40272233
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

Habib Pourfard earned 2000 total points
ID: 40272244
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

ID: 40272502

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

830 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