8 Replies - 982 Views - Last Post: 23 August 2019 - 06:19 AM Rate Topic: -----

#1 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 06-March 09

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

Posted 22 August 2019 - 12:03 AM

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()



Is This A Good Question/Topic? 0
  • +

Replies To: How to update rows value of specific column from another table's s

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,229
  • Joined: 12-December 12

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

Posted 22 August 2019 - 12:41 AM

You aren't creating/inserting records, you need an UPDATE statement to update existing rows. Include a JOIN to help resolve which rows will be updated.

(I have suspicions, though, about why you would base these updates on a first-name match. Perhaps this is just an example.)
Was This Post Helpful? 0
  • +
  • -

#3 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 06-March 09

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

Posted 22 August 2019 - 02:30 AM

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.

This post has been edited by andrewsw: 22 August 2019 - 04:22 AM
Reason for edit:: removed redundant quote

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,229
  • Joined: 12-December 12

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

Posted 22 August 2019 - 04:24 AM

Note that there is no need to quote your original post in full, use the Reply button further down the page.

Did you read my previous post? Post your attempt to use an UPDATE statement. Preferably, explore and test an update directly in MS Access.
Was This Post Helpful? 0
  • +
  • -

#5 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 06-March 09

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

Posted 22 August 2019 - 07:26 AM

scard and Employee_Records are tables
empnum and FirstName(field/column of scard)
Employee_Number and First_Name(field/column of Employee_Records)

UPDATE scard INNER JOIN Employee_Records
    ON scard.FirstName = Employee_Records.First_Name
SET scard.empnum = Employee_Records.Employee_Number


This post has been edited by dpointer: 22 August 2019 - 07:28 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15262
  • View blog
  • Posts: 61,197
  • Joined: 12-June 08

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

Posted 22 August 2019 - 07:35 AM

You would use the SQL text in a SQLCommand object or what ever.

Examples:
https://www.dreaminc...-adapter-setup/
https://www.dreaminc...-command-setup/
Was This Post Helpful? 0
  • +
  • -

#7 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,209
  • Joined: 12-January 10

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

Posted 22 August 2019 - 07:36 AM

Dim queryString As String = "UPDATE scard INNER JOIN Employee_Records ON scard.FirstName = Employee_Records.First_Name SET scard.empnum = Employee_Records.Employee_Number"

Was This Post Helpful? 1
  • +
  • -

#8 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 06-March 09

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

Posted 22 August 2019 - 10:31 PM

View PostDarenR, on 22 August 2019 - 02:36 PM, said:

Dim queryString As String = "UPDATE scard INNER JOIN Employee_Records ON scard.FirstName = Employee_Records.First_Name SET scard.empnum = Employee_Records.Employee_Number"

Thank you very much my friend Daren. It works...
Was This Post Helpful? 0
  • +
  • -

#9 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 06-March 09

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

Posted 23 August 2019 - 06:19 AM

View PostDarenR, on 22 August 2019 - 02:36 PM, said:

Dim queryString As String = "UPDATE scard INNER JOIN Employee_Records ON scard.FirstName = Employee_Records.First_Name SET scard.empnum = Employee_Records.Employee_Number"

Thanks my friend Daren. It works..
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1