Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2014
    Posts
    16

    Unanswered: Compare update on two tables

    I have 2 tables. They both have the same fields [OFO_Code] and [Description]

    Table 1 is Called Qualification and Table 2 OFO_Code. The second table is a lookup table. The info in Table 1 's description column in some places does not match that of Table 2.

    I am trying to loop through these tables to Update the info in Table 1's description to match the correct description in Table 2.
    Their are 1417 items in Table 2 and 77000 items in table 1.

    There are NO errors when executing, just no update
    Here is my code so far:

    Code:
    Update Qualification SET [Description]  = B.Description    FROM  Qualification A  INNER JOIN OFO_code B ON A.OFO_CODE = B.OFO_CODE WHERE A.OFO_Code IN ('" & OFO & "' )"

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You do not need to store the description in the Qualification table.
    When you need that value look it up from the OFO_code table
    Code:
    SELECT qualification.*
         , ofo_code.description As ofo_code_description
    FROM   qualification
     LEFT
      JOIN ofo_code
        ON ofo_code.ofo_code = qualification.ofo_code
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2014
    Posts
    16

    Two tables

    Yes, I have done this ...

    Code:
     FieldString2 = "SELECT   a.[OFO_Code] as [ OFO Code] ," & GenderCode & " as [Gender] ," & EquityCode & "  as [Equity] , " & _
                                     "" & ProvinceCode & " as [Province Name], c.[Description] AS [Trade description], year(a.[DateComp])  as [ Year] , " & _
                                     "DateName(month, DateAdd(month , Month(a.[DateComp]), -1 )) as [ Month] ,  count(year(a.[DateComp])) as [# per Month] " & _
                                     "FROM Qualification a INNER JOIN Student  b on a.[id] = b.[id]  JOIN  OFO_COde  c ON a.OFO_Code = c.Ofo_Code       " & _
                                     "WHERE a.[DateComp] BETWEEN '" & YearSelected & "' AND '" & YearEnd & "' " & _
                                     "GROUP BY year(a.[Datecomp]), Month(a.[DateComp])   ,a.[OFO_Code], a.[Description],  b.[Gender_Code] , " & _
                                     "b.[Equity_Code], b.[Province_Code], c.[Description] ORDER BY  year(a.[Datecomp])"
    But I want to know how I can fix my tables, even If it is only for interest

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This should probably do it
    Code:
    UPDATE qualification
    SET    description = ofo_code.description
    FROM   qualification
     INNER
      JOIN ofo_code
        ON ofo_code.ofo_code = qualification.ofo_code
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2014
    Posts
    16

    2 tables update

    no, that does not work either

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "does not work" is a little too vague to diagnose...

    Error message or description as to what isn't working would be handy. Include your *exact* code too
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2014
    Posts
    16

    compare update on two tables

    It does not return an error, it simply does not change the data.

    I am writing in VB.NET

    Code:
     cmd2.Connection = con
            For a = 0 To 1416 ' amount of items in Array
                OFO = CInt(OFOCODE_Array(a, 0))  ' This is a 2 dimen array.... (a,1) is not relevant here
                Dim query2 As String = "UPDATE qualification  description = ofo_code.description      FROM qualification    INNER   Join  ofo_code   ON ofo_code.ofo_code = qualification.ofo_code  WHERE OFO_Code =('" & OFO & "' ) "
                Using conn As New SqlConnection(constr)
                    Using cmd2 As New SqlClient.SqlCommand(query2, conn)
                        ' Not using parameter  here , cause getting error when doing batch update
                        conn.Open()
                        cmd.ExecuteNonQuery()
                    End Using
                End Using
            Next

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you tried running the query directly in SSMS?
    Failing that print out the value of the query prior to execution.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •