• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Update SQL Cursor help needed.

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
Jeff S
Asked:
Jeff S
  • 5
  • 5
  • 2
  • +1
2 Solutions
 
Philippe DamervalSenior Analyst ProgrammerCommented:
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
 
Jeff SAuthor Commented:
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
 
Evan CutlerVolunteer Chief Information OfficerCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Jeff SAuthor Commented:
Incorrect syntax near 'PC'.

UPDATE PatientCorrespondence PC
Join dbo.PatientVisit PV on PC.PatientVisitId  = PV.PatientVisitId
Set PC.Description = PV.note
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
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
 
Philippe DamervalSenior Analyst ProgrammerCommented:
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
 
Evan CutlerVolunteer Chief Information OfficerCommented:
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
 
Jeff SAuthor Commented:
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
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Ah - I stand corrected :) This is a case where copying data is warranted...
The second statement I sent should do it for you.
0
 
Scott PletcherSenior DBACommented:
UPDATE pc
SET pc.description = CONVERT(varchar(255), pv.Notes)
FROM PatientCorrespondence pc
INNER JOIN PatientVisit pv ON
    pc.PatientVisitID = pv.PatientVisitID
0
 
Jeff SAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Philippe DamervalSenior Analyst ProgrammerCommented:
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
 
Jeff SAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now