How to re-write this in SQL?

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
Taz2Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
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
 
worldsergaCommented:
Hi

Can you post these three SQL tables??


0
 
rafranciscoCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Taz2Author Commented:
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
 
worldsergaCommented:
So do you have any problem with your query??
0
 
Taz2Author Commented:
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
 
rafranciscoCommented:
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
 
Taz2Author Commented:
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
 
Taz2Author Commented:
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
 
rafranciscoCommented:
It will just retain the original value.
0
 
Taz2Author Commented:
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
 
Taz2Author Commented:
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
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.

All Courses

From novice to tech pro — start learning today.