dcsimg
Results 1 to 7 of 7

Thread: How to convert this piece of sql code to oledb command?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    How to convert this piece of sql code to oledb command?

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



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

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,361

    Re: How to convert this piece of sql code to oledb command?

    Code:
    UPDATE S SET
         S.empnum = ER.Employee_Number
    FROM scard S
    INNER JOIN Employee_Records ER
       ON s.FirstName = ER.First_Name
    BUT What happens when you have 2 people with the same first NAME (John) then you really don't know what you get in the end
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,232

    Re: How to convert this piece of sql code to oledb command?

    I totally agree with what Gary said, as this SQL seems likely to be a really bad idea, but to answer the question:
    Code:
    yourCommandObject.CommandText = "UPDATE S SET S.empnum = ER.Employee_Number FROM scard S INNER JOIN Employee_Records ER ON s.FirstName = ER.First_Name
    yourCommandObject.ExecuteNonQuery()
    Of course, you might want to get the return value from ExecuteNonQuery, which will be the number of records affected.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    Re: How to convert this piece of sql code to oledb command?

    Quote Originally Posted by GaryMazzone View Post
    Code:
    UPDATE S SET
         S.empnum = ER.Employee_Number
    FROM scard S
    INNER JOIN Employee_Records ER
       ON s.FirstName = ER.First_Name
    BUT What happens when you have 2 people with the same first NAME (John) then you really don't know what you get in the end
    Thank you Gary for responding my concern. Actually it should be done by finding Fullname and Birthday for the exact. FirstName there is just an example. Any idea? I would highly appreciate your ideas and suggestions.

  5. #5
    New Member
    Join Date
    May 2016
    Location
    Argentina
    Posts
    6

    Re: How to convert this piece of sql code to oledb command?

    Actually, you should have an additional field that contains an ID that identifies the user, it can be a number, a string or even use a value that identifies it (in case of a person, something like social security number).

    Examples:
    Code Name Last Name Birthday Role
    1 John Becket 10/02/1985 Supervisor
    2 John Smith 10/02/1975 Employe
    3 John Becket 10/02/1985 Employee

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    Resolved Re: How to convert this piece of sql code to oledb command?

    Quote Originally Posted by lucaszoft View Post
    actually, you should have an additional field that contains an id that identifies the user, it can be a number, a string or even use a value that identifies it (in case of a person, something like social security number).

    Examples:
    code name last name birthday role
    1 john becket 10/02/1985 supervisor
    2 john smith 10/02/1975 employe
    3 john becket 10/02/1985 employee
    already solved. Thanks

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,361

    Re: How to convert this piece of sql code to oledb command?

    Read about SQL Joins (https://www.w3schools.com/sql/),,,,, you just add conditions to the inner join
    Last edited by GaryMazzone; Aug 23rd, 2019 at 06:59 AM.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

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
  •  



Featured


Click Here to Expand Forum to Full Width