?
Solved

How to re-write this in SQL?

Posted on 2005-05-11
12
Medium Priority
?
219 Views
Last Modified: 2010-03-19
Hi everyone,

Can someone please show me how to re-write this into a SQL statement?

Set rsContactRS = dbMyDB.OpenRecordset("CSL_CNT_STUDENT_CONTACT", dbOpenDynaset)
Set rsSISRS = dbMyDB.OpenRecordset("CSL_CNT_STUDENT_SIS_MAP", dbOpenDynaset)
Set rsContDemoRS = dbMyDB.OpenRecordset("CSL_CNT_CONTACT", dbOpenDynaset)

rsSISRS.MoveFirst
rsContactRS.MoveFirst
rsContDemoRS.MoveFirst

    Do While Not rsContactRS.EOF
        position = 0
        rsSISRS.MoveFirst
        rsContDemoRS.MoveFirst
        currentcontact = rsContactRS!ID_CNT_CONTACT
        rsContDemoRS.FindFirst "ID_CNT_Contact = " & currentcontact
        If rsContDemoRS!ID_SET_CONTACT_TYPE = 5006 Then position = 1
        If rsContDemoRS!ID_SET_CONTACT_TYPE = 5007 Then position = 2
        If rsContDemoRS!ID_SET_CONTACT_TYPE = 5005 Then position = 3
        If rsContDemoRS!ID_SET_CONTACT_TYPE = 5003 Then position = 4
        If rsContDemoRS!ID_SET_CONTACT_TYPE = 5001 Then position = 5
        If rsContDemoRS!ID_SET_CONTACT_TYPE = 5002 Then position = 6
        currentstudent = rsContactRS!ID_STD_DEMO_DISTRICT
        If position = 1 Then
            rsSISRS.FindFirst "ID_STD_DEMO_DISTRICT = " & currentstudent
            rsSISRS.Edit
            rsSISRS!ID_CNT_CONTACT = currentcontact
            rsSISRS.Update
        End If
        If position <> 0 And position <> 1 Then
            rsSISRS.FindFirst "ID_STD_DEMO_DISTRICT = " & currentstudent
            For x = 1 To position - 1
            rsSISRS.FindNext "ID_STD_DEMO_DISTRICT = " & currentstudent
            Next x
            rsSISRS.Edit
            rsSISRS!ID_CNT_CONTACT = currentcontact
            rsSISRS.Update
        End If
        rsContactRS.MoveNext
    Loop

What I am trying to do is update the "ID_CNT_CONTACT" field in the "CSL_CNT_STUDENT_SIS_MAP" table with the "ID_CNT_CONTACT" value from the  "CSL_CNT_STUDENT_CONTACT" table based on the "ID_SET_CONTACT_TYPE" from the "CSL_CNT_CONTACT" table.  I am not as fluent in SQL and would greatly appreciate some help.  TIA.
Taz2
0
Comment
Question by:Taz2
  • 6
  • 4
  • 2
12 Comments
 
LVL 1

Expert Comment

by:worldserga
ID: 13987322
Hi

Can you post these three SQL tables??


0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13987779
Your SQL statement may look like this:

UPDATE A
SET ID_CNT_CONTACT = B.ID_CNT_CONTACT
FROM CSL_CNT_STUDENT_SIS_MAP A INNER JOIN CSL_CNT_STUDENT_CONTACT B
   ON A.ID_SET_CONTACT_TYPE = B.ID_SET_CONTACT_TYPE

But we will be needing the relationship between your 3 tables.
0
 

Author Comment

by:Taz2
ID: 13988929
These are the tables:

CSL_CNT_STUDENT_SIS_MAP: ID (PK)
                                              ID_STD_DEMO_DISTRICT
                                              ID_CND_CONTACT
                                              SIS_SLOT

CSL_CNT_STUDENT_CONTACT:  ID (PK)
                                               ID_STD_DEMO_DISTRICT
                                               ID_CNT_CONTACT

CSL_CNT_CONTACT:   ID_CNT_CONTACT (PK)
                                  ID_SET_CONTACT_TYPE
 

This table "CSL_CNT_STUDENT_SIS_MAP" exists only for the purpose of assigning Contacts to Position - each student has 6 contact slots in this table (SIS_SLOT: 1-6) and we are assigning a contact number to a slot for a student, a person can be a contact for more than one student and in a different position for each.  This is my query so far:

UPDATE CSL_CNT_STUDENT_SIS_MAP
SET CSL_CNT_STUDENT_SIS_MAP.ID_CNT_CONTACT = CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT
FROM CSL_CNT_STUDENT_SIS_MAP, CSL_CNT_STUDENT_CONTACT
WHERE CSL_CNT_STUDENT_SIS_MAP.ID_STD_DEMO_DISTRICT = CSL_CNT_STUDENT_CONTACT.ID_STD_DEMO_DISTRICT
AND CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT = (SELECT ID_CNT_CONTACT FROM CSL_CNT_CONTACT WHERE ID_SET_CONTACT_TYPE IN (5001, 5002, 5003, 5005, 5006, 5007))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:worldserga
ID: 13989029
So do you have any problem with your query??
0
 

Author Comment

by:Taz2
ID: 13989314
Well I know my sub-query will give me an error because it will return more than one record (this was confirmed when i ran the query) and I don't think I can use IN because I want to MATCH the "ID_CNT_CONTACT" in the "CSL_CNT_STUDENT_CONTACT" table to the "ID_CNT_CONTACT" in the "CSL_CNT_CONTACT" for those ID_SET_CONTACT_TYPE.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13989549
Try this one:

UPDATE CSL_CNT_STUDENT_SIS_MAP
SET CSL_CNT_STUDENT_SIS_MAP.ID_CNT_CONTACT = CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT
FROM CSL_CNT_STUDENT_SIS_MAP INNER JOIN CSL_CNT_STUDENT_CONTACT
ON CSL_CNT_STUDENT_SIS_MAP.ID_STD_DEMO_DISTRICT = CSL_CNT_STUDENT_CONTACT.ID_STD_DEMO_DISTRICT
INNER JOIN CSL_CNT_CONTACT
ON CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT = CSL_CNT_CONTACT.ID_CNT_CONTACT AND
   CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE IN (5001, 5002, 5003, 5005, 5006, 5007)
0
 

Author Comment

by:Taz2
ID: 13990106
Ok...I now had modified this statement to includu assigning SLOT to "Contact Type".  This is my statement:

UPDATE CSL_CNT_STUDENT_SIS_MAP
SET CSL_CNT_STUDENT_SIS_MAP.ID_CNT_CONTACT = CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT,
    CASE WHEN CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE = 5006 THEN CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT = 1
         WHEN CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE = 5007 THEN CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT = 2
       WHEN CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE = 5005 THEN CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT = 3
       WHEN CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE = 5003 THEN CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT = 4
       WHEN CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE = 5001 THEN CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT = 5
       WHEN CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE = 5002 THEN CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT = 6
    END
FROM CSL_CNT_STUDENT_SIS_MAP
      INNER JOIN CSL_CNT_STUDENT_CONTACT
      ON CSL_CNT_STUDENT_SIS_MAP.ID_STD_DEMO_DISTRICT = CSL_CNT_STUDENT_CONTACT.ID_STD_DEMO_DISTRICT
      INNER JOIN CSL_CNT_CONTACT
      ON CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT = CSL_CNT_CONTACT.ID_CNT_CONTACT

I keep getting this error: "Incorrect syntax near the keyword 'CASE'.".  Why?
      
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13990177
Try this:

UPDATE CSL_CNT_STUDENT_SIS_MAP
SET CSL_CNT_STUDENT_SIS_MAP.ID_CNT_CONTACT = CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT,
      CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT =
           CASE CSL_CNT_CONTACT.ID_SET_CONTACT_TYPE
                      WHEN 5006 THEN 1
                      WHEN 5007 THEN 2
                      WHEN 5005 THEN 3
                      WHEN 5003 THEN 4
                      WHEN 5001 THEN 5
                      WHEN 5002 THEN 6
                      ELSE CSL_CNT_STUDENT_SIS_MAP.SIS_SLOT
            END
FROM CSL_CNT_STUDENT_SIS_MAP
     INNER JOIN CSL_CNT_STUDENT_CONTACT
     ON CSL_CNT_STUDENT_SIS_MAP.ID_STD_DEMO_DISTRICT = CSL_CNT_STUDENT_CONTACT.ID_STD_DEMO_DISTRICT
     INNER JOIN CSL_CNT_CONTACT
     ON CSL_CNT_STUDENT_CONTACT.ID_CNT_CONTACT = CSL_CNT_CONTACT.ID_CNT_CONTACT
0
 

Author Comment

by:Taz2
ID: 13990371
Wow, your query is definitely better written :).  I have one last question regarding the CASE, if the ID_SET_CONTACT_TYPE does not fall in that range (5001, 5002, etc...), does that mean that record will not be updated?  I think so.  TIA
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13990382
It will just retain the original value.
0
 

Author Comment

by:Taz2
ID: 13990423
Thank you.  I am going to test this and see if the results are what I am looking for, then I will close out my question.
0
 

Author Comment

by:Taz2
ID: 14000032
Hi Rafrancisco,

Ok.  I have been trying to get this sql to work and so far no luck.  I have more info and hoping it may help:  This is what my data look like:

Std_ID      District       Type        Contact     POS
5000      11220      5006      NULL      3
5000      11220      5006      NULL      4
5000      11220      5006      NULL      5
5000      11220      5006      NULL      6
5002      11221      5006      NULL      5
5002      11221      5006      NULL      6
5004      11221      5002      NULL      5
5004      11221      5002      NULL      6

I need to update "Contact" and also POS, the value for POS is based on TYPE (see sql above).  There is a Unique Key Constraint on Type, Contact and POS.  How do I go about updating "Contact" and POS with this type of constraint?  TIA

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

807 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