Results 1 to 4 of 4

Thread: How to update rows value of specific column from another table's specific column?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    How to update rows value of specific column from another table's specific column?

    Hi friends. I'm new into this combination of sql query. I want to update all rows value of column "empnum" of table "scard" from column "Employee_Number"
    of table "Employee_Records". If two(2) columns, the "FirstName" of table "scard" and "First_Name" of table "Employee_records" have the same value(like for example they have same value: John) then the value of column "Employee_Number" from another table will reflect to "empnum". I'm using MS Access database. Thank you for responding my query.

    Illustration for expected result:

    Table Employee_Records
    --------------------------------------------------
    Employee_Number | First_Name
    --------------------------------------------------
    01234 | John
    65478 | Jade
    --------------------------------------------------

    Table scard
    --------------------------------------------------
    empnum | FirstName
    --------------------------------------------------
    | James
    01234 | John
    | Rob
    65478 | Jade
    | Mark
    --------------------------------------------------

    Below is the code that did not work.
    Code:
            Dim sSQL As String
          
            '------------------------------------------------------------------------------------------
            sSQL = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ERIS.accdb"
            Dim conn As OleDbConnection = New OleDbConnection(sSQL)
            sSQL = "INSERT INTO scard (empnum) values (@empnum) "
            sSQL = sSQL & "SELECT Employee_Number "
            sSQL = sSQL & "FROM Employee_Records "
            sSQL = sSQL & "WHERE (First_Name) values (@First_Name) "
            Dim command As New OleDbCommand(sSQL, conn)
            conn.Open()
            command.ExecuteNonQuery()
            conn.Close()
    Last edited by ronelpisan; Aug 22nd, 2019 at 12:33 AM. Reason: added illustration

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to update rows value of specific column from another table's specific column?

    First of all, do you want to add new rows or modify existing rows? If it's the latter then you need to use an UPDATE statement, not an INSERT statement.

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to update rows value of specific column from another table's specific column?

    Quote Originally Posted by jmcilhinney View Post
    First of all, do you want to add new rows or modify existing rows? If it's the latter then you need to use an UPDATE statement, not an INSERT statement.
    I don't want to insert or add new record, I want to modify/update existing rows.
    I just want to update existing rows of "empnum" field of "scard" table with the value from "Employee_Number" of "Employee_Records" table.
    Please help me to revise the codes. Thank you.
    Last edited by ronelpisan; Aug 22nd, 2019 at 04:19 AM. Reason: explanation

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to update rows value of specific column from another table's specific column?

    If you want to modify existing records then, as I said, you need to execute a SQL UPDATE statement, not an INSERT statement. You should do some research on that.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width