Solved

Update SQL Cursor help needed.

Posted on 2012-04-04
14
383 Views
Last Modified: 2012-04-04
I need assistance with an update script (cursor).

Basically, what I have is an entry in one table that I need one another table. What is currently found on the PatientVisit.Note field, I need copied and placed into the PatientCorrespondence.Description field.

The data types are as follows:

PatientVisit.Notes - Text
PatientCorrespondence.Description - VARCHAR(255)

The PatientVisitId is FK relationship back to the PatientVisit table from the PatientCorrespondence table.

I will need to do an insert into my PatientCorrespondence table where the patientvisitIds match and insert the value that was in my patientvisit.notes into the description below.

INSERT INTO dbo.PatientCorrespondence
            (
              PatientVisitId ,
              PatientProfileId ,
              Source ,
              UserId ,
              Description ,
              DescriptionLong ,
              NextContactDate ,
              Created ,
              CreatedBy ,
              LastModified ,
              LastModifiedBy
            )

Open in new window

0
Comment
Question by:Jeff S
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 9

Expert Comment

by:damerval
ID: 37807267
Jeff,
Your question is a little unclear. What exactly are you having trouble with? Do you mean to insert data from one table into another every time a row is added into the second one? If so, what you are looking at is a trigger. I am also unclear on what usage you are making of cursors. Nothing in your question evokes the need for a cursor. I could be misled. Please reply with a clearer description of your target goal and what is currently preventing you from reaching it.
Thanks!

Philippe
0
 
LVL 7

Author Comment

by:Jeff S
ID: 37807297
Sorry about that ...

Example: In PatientVisit.Note I may have an entry on a PatientVisit like so:

"This is my note from the PatientVisit.Note field"

What I need to do is take the note from this field and copy it over into the PatientCorrespondence.Description field.

For all Patient Visits in the Database, this one special time that have an entry in that field. If I can avoid the cursor, I will be much happier, but I think I may have to use one here.
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37807314
Based on what you have, are you saying that the child table (PatientCorrespondance) has values already, and you want to just update the Description Field?

if so, You don't want an insert statement, you want an update statement...

UPDATE dbo.PatientCorrespondance PC
Join dbo.PatientVisit PV on PC.PatientVisitId  = PV.PatientVisitId 
Set PC.Description = PV.note

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 37807331
Incorrect syntax near 'PC'.

UPDATE PatientCorrespondence PC
Join dbo.PatientVisit PV on PC.PatientVisitId  = PV.PatientVisitId
Set PC.Description = PV.note
0
 
LVL 9

Expert Comment

by:damerval
ID: 37807405
If you know for certain that there is already a patient correspondence record for a patient visit, and you have the PatientVisitID, then issue the following statement:

UPDATE PatientCorrespondence
SET description = (SELECT Notes FROM PatientVisit WHERE PatientVisitID=MyID)
WHERE PatientVisitID = MyID

Note that this will truncate the notes field to the first 255 characters.

If you want to do this for ALL note fields, issue this:

UPDATE PatientCorrespondence
SET description = PatientVisit.Notes
FROM PatientCorrespondence INNER JOIN PatientVisit ON PatientCorrespondence.PatientVisitID = PatientVisit.PatientVisitID
0
 
LVL 9

Expert Comment

by:damerval
ID: 37807408
But I'm curious, why copy data from one table to another like this? It violates normal form. Given your business need, I am sure there is a good way to fill it without copying data. Tell us more and we might be able to give you a more elegant solution.

Thanks,

Philippe
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37807410
sorry, I'm in oracle trying to talk SQL Server:

try this:

UPDATE PatientCorrespondence PC
Set PC.Description = PV.note
From Join PatientVisit PV
WHERE PC.PatientVisitId  = PV.PatientVisitId 

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Author Comment

by:Jeff S
ID: 37807421
Phillipe -

This is being done because the group was inputting the note in the wrong location and need it to be moved for all past mistakes.
0
 
LVL 9

Expert Comment

by:damerval
ID: 37807445
Ah - I stand corrected :) This is a case where copying data is warranted...
The second statement I sent should do it for you.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37807489
UPDATE pc
SET pc.description = CONVERT(varchar(255), pv.Notes)
FROM PatientCorrespondence pc
INNER JOIN PatientVisit pv ON
    pc.PatientVisitID = pv.PatientVisitID
0
 
LVL 7

Author Comment

by:Jeff S
ID: 37807544
All,

The reason why I first went to INSERT INTO versus the update, was the issue if there were prior entries in the PatientCorrespondence table for the patientvisitId. For grins, I input a value in the GUI in the PatientVisit.Notes field and then I went in the PatientCorrespondence.Description and input another note (not the same not, something unique in each).

When I ran the script in my demo database it wiped out the entry in my PatientCorrespondence field and updated the value. There may be instances when an entry is already in that field and I do not want to UPDATE it, rather insert the new record.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 37807591
INSERT INTO dbo.PatientCorrespondence
            (
              PatientVisitId ,
              PatientProfileId ,
              Source ,
              UserId ,
              Description ,
              DescriptionLong ,
              NextContactDate ,
              Created ,
              CreatedBy ,
            )
SELECT pv.PatientVisitId, ...,  CONVERT(varchar(255), pv.Notes), ...
FROM dbo.PatientVisit pv
INNER JOIN dbo.PatientCorrespondence pc ON
    pc.PatientVisitID = pv.PatientVisitID
0
 
LVL 9

Accepted Solution

by:
damerval earned 250 total points
ID: 37807624
To be clear - do you want to:

- Only update the correspondence record if there is no existing text in the description field
- Always insert a new record for all values in the notes field of the visits table
- Either update the correspondence record for a visit if it is null, or add a new record if it is not?

In the last two cases, what values would you set for the other fields in the correspondence table?

Thanks,

Philippe
0
 
LVL 7

Author Comment

by:Jeff S
ID: 37808032
I modified the code some by adding in the WHERE clause condition. This seems to do what I need it to do.

Phillipe - to answer your question, I am only adding into the PatientCorrespondence.Description if I find an entry in the PatientVisit.Note field. If there is not an entry in the Notes, I have nothing to add to it, therefore its skipped. I am not updating in the reverse, only when there is something in notes am I inserting into patientcorrespondence. What I have in my code snippet seems to work. I think unless anyone sees any issues.

INSERT  INTO dbo.PatientCorrespondence
        (
          PatientVisitId ,
          PatientProfileId ,
          Source ,
          UserId ,
          Description ,
          DescriptionLong ,
          NextContactDate ,
          Created ,
          CreatedBy ,
          LastModified ,
          LastModifiedBy
			
        )
        SELECT
            pv.PatientVisitId ,
            pv.PatientProfileId ,
            0 ,
            'Jeff' ,
            CONVERT(VARCHAR(255) , pv.Notes) ,
            '' ,
            '' ,
            GETDATE() ,
            'Jeff' ,
            GETDATE() ,
            'Jeff'
        FROM
            dbo.PatientVisit pv
            LEFT JOIN dbo.PatientCorrespondence pc ON pc.PatientVisitID = pv.PatientVisitID
        WHERE
            pv.Notes IS NOT NULL

Open in new window

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

20 Experts available now in Live!

Get 1:1 Help Now