Solved

Update SQL Cursor help needed.

Posted on 2012-04-04
14
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 9

Expert Comment

by:Philippe 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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:Philippe 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:Philippe 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
 
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:Philippe 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:Scott Pletcher
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:Scott Pletcher
Scott Pletcher 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:
Philippe 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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 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