none
How to Update Join Tables ? RRS feed

  • Question

  • How to Update join Tables ?
    I have 4 sql Tables :
    dbStage1, dbStage2,dbStage3,dbStage3.
    joined by full outer join , Items of this join are inserted in a new Table named dbStages by button click in the sample of code:

    Imports System.Data
    Imports System.Data.SqlClient
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim sql As String = "Select (DbStage1.CID) As CID 
                                  ,(DbStage1.Villes)As Villes 
                                  ,(DbStage1.Candidats) As Candidats
                                  ,ISNULL(DbStage1.Points,0) As Stage1
                                  ,ISNULL(DbStage2.Points,0) As Stage2
                                  ,ISNULL(DbStage3.Points,0) As Stage3
                                  ,ISNULL(DbStage4.Points,0) As Stage4 from ((DbStage1 FULL OUTER JOIN DbStage2 ON DbStage1.CID=DbStage2.CID) FULL OUTER JOIN DbStage3  On DbStage2.CID= DbStage3.CID)FULL OUTER JOIN DbStage4 On dbStage3.CID=DbStage4. CID Order by DbStage1.CID"
            Using con As New SqlConnection(cs2)
                Dim dt As New DataTable
                Using ad As New SqlDataAdapter(sql, con)
                    ad.Fill(dt)
                    Dim insertstring = "Insert into DbStages (CID,Villes,Candidats,Stage1,Stage2,Stage3,Stage4) Values (@d1, @d2, @d3,@d4,@d5,@d6,@d7)"
                    Using com As New SqlCommand(insertstring, con)
                        con.Open()
                        For Each row As DataRow In dt.Rows
                            com.Parameters.Clear()
                            For Each column In dt.Columns
                                com.Parameters.Clear()
                                com.Parameters.AddWithValue("@d1", row.Item("CID"))
                                com.Parameters.AddWithValue("@d2", row.Item("Villes"))
                                com.Parameters.AddWithValue("@d3", row.Item("Candidats"))
                                com.Parameters.AddWithValue("@d4", row.Item("Stage1"))
                                com.Parameters.AddWithValue("@d5", row.Item("stage2"))
                                com.Parameters.AddWithValue("@d6", row.Item("stage3"))
                                com.Parameters.AddWithValue("@d7", row.Item("Stage4"))
                            Next
                            com.ExecuteNonQuery()
                        Next
                    End Using
                End Using
            End Using
            MessageBox.Show("Successfully Saved", "In db-Stages", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Sub

    My question is how to Update the table 'dbStages' When the tables (dbStage1, dbStage2,dbStage3,dbStage3) of the join are updated?

    tHank you very Much,

    Best Regards , Bajtitou.

    Wednesday, April 25, 2018 9:59 AM

All replies

  • First of all: Use table alias names and remove unnecessary parentheses.

    SELECT   DS1.CID ,
             DS1.Villes ,
             DS1.Candidats ,
             DS1.Points AS Stage1Points ,
             ISNULL(DS2.Points, 0) AS Stage2Points ,
             ISNULL(DS3.Points, 0) AS Stage3Points ,
             ISNULL(DS4.Points, 0) AS Stage4Points
    FROM     DbStage1 DS1
             FULL OUTER JOIN DbStage2 DS2 ON DS1.CID = DS2.CID
             FULL OUTER JOIN DbStage3 DS3 ON DS2.CID = DS3.CID
             FULL OUTER JOIN DbStage4 DS4 ON DS3.CID = DS4.CID
    ORDER BY DS1.CID;

    But: Why do you need a FULL JOIN?  Seems like an ordinary hierarchy, thus you should use a LEFT JOIN instead:

    SELECT   DS1.CID ,
             DS1.Villes ,
             DS1.Candidats ,
             DS1.Points AS Stage1Points ,
             ISNULL(DS2.Points, 0) AS Stage2Points ,
             ISNULL(DS3.Points, 0) AS Stage3Points ,
             ISNULL(DS4.Points, 0) AS Stage4Points
    FROM     DbStage1 DS1
             LEFT JOIN DbStage2 DS2 ON DS1.CID = DS2.CID
             LEFT JOIN DbStage3 DS3 ON DS2.CID = DS3.CID
             LEFT JOIN DbStage4 DS4 ON DS3.CID = DS4.CID
    ORDER BY DS1.CID;

    Then: a simple update is not possible due to your data model. You need a MERGE or INSERT/UPDATE for each of the subsequent tables (DbStage2 - DbStage4). Cause there may be no rows in it.




    Wednesday, April 25, 2018 10:23 AM
  • You cannot. 

    You can use a joined table to present on paper or on screen. But not as a base for updating. 

    Therefore you need to retrieve and update all the related separate tables. 

    (Or it should be many insert delete and update SQL commands executes with executenonquery)

    But then we are talking about almost impossible to maintain code. And certainly no general way which can be shown in a forum. 


    Success
    Cor


    Wednesday, April 25, 2018 11:35 AM

  • Then: a simple update is not possible due to your data model. You need a MERGE or INSERT/UPDATE for each of the subsequent tables (DbStage2 - DbStage4). Cause there may be no rows in it.




    Hi, 

    THank you for the answer, yes it's an ordinary hierarchy cause evry candidat had to pass by evry stage (1,2,3,4).

    The following tables (dbstage1 ,,, dbstage4) are previously updated separately in other forms  and it contains rows.

    So ,Sir have any solution to propose for this and how to Merge programmatically the 4 tables??

    thank you very much ,

    Best Regards, Bajtitou. 

    Wednesday, April 25, 2018 1:10 PM
  • You cannot. 

    You can use a joined table to present on paper or on screen. But not as a base for updating. 

    Therefore you need to retrieve and update all the related separate tables. 

    (Or it should be many insert delete and update SQL commands executes with executenonquery)

    But then we are talking about almost impossible to maintain code. And certainly no general way which can be shown in a forum. 


    Success
    Cor


    Hi,

    Thank you very Much for your Answer ,

    I searched for anywhere and found nothing,

    so is there any way to do that?

    thank you,

    Best regards ,Bajtitou.

    Wednesday, April 25, 2018 1:15 PM
  • What you need are the primary keys for tables you have joined together as this is how you would do your update, without the keys you can't update.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 25, 2018 2:06 PM
    Moderator


  • Hi,

    Thank you very Much for your Answer ,

    I searched for anywhere and found nothing,

    so is there any way to do that?

    thank you,

    Best regards ,Bajtitou.

    That also proofs more you cannot do it. Keep joined tables for presentation. 

    If you need updates than try to update all tables separately. 

    (You can use 2 related tables for instance if it is about orders and order rows)

    But that means that you create one row for the orders and the order rows in a datagridview


    Success
    Cor


    Wednesday, April 25, 2018 5:41 PM
  • What you need are the primary keys for tables you have joined together as this is how you would do your update, without the keys you can't update.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Hello,

    thank you for your answer ,

    Yes , of cause, all Tables have a primary keys ,

    this is the Data Base Test I am working With : download it from one drive:

    DB_Test

    So how to Update The DbStages When ( dbstage1,dbstage2,dbstage3,dbstage4) will be updated??

    Thank you very much ,

    Best Regards , Bajtitou.


    • Edited by Bajtitou Wednesday, April 25, 2018 7:30 PM
    Wednesday, April 25, 2018 6:52 PM

  • That also proofs more you cannot do it. Keep joined tables for presentation. 

    If you need updates than try to update all tables separately. 

    (You can use 2 related tables for instance if it is about orders and order rows)

    But that means that you create one row for the orders and the order rows in a datagridview



    Hi,

    The tables are previously  Updated Separately , 
    I want to take one column (column Points) from each table et build une DB named dbStages,whitch will be Updated every time when tables mothers have been Updated,That Is the Problem.

    here is the DB Test I am working whith :download it from one drive

    DB_Test

    So , thank you very much Sir;

    Best Regards ,Bajtitou.


    • Edited by Bajtitou Wednesday, April 25, 2018 7:30 PM
    Wednesday, April 25, 2018 7:27 PM
  • Hi Bajtitou,

    According to your code and table, you have four tables like dbStage1,dbStage2,dbStage3,dbStage4, the CID in every table and connect then each other.

    Now you insert some data from these four table into dbStage, it also have CID. SO if you update some data in every table, you can use CID to query data from dbStage and update it.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 5:48 AM
    Moderator

  • you can use CID to query data from dbStage and update it.


    Hi Cherry,

     Thank you for your answer ,

    How to do It?

    what is the Query , how to add parameters ...??

    Thank you very much,

    Best Regards , Bajtitou.

    Thursday, April 26, 2018 6:37 AM
  • Cherry,


    With a datatable you have to know from every datarow if it is an insert, an update or a delete (which depends on all childs and masters in all tables on every level.  

    I've never seen this what you tell, so it will probably be a revolutionary invention, maybe can you show this in Redmond how you did this in a generic way.

    Or is it lack of knowledge and do you think that update means the same as insert. The question is "How to Update join Tables ?"


    Success
    Cor



    Thursday, April 26, 2018 9:53 AM