-
Aug 22nd, 2019, 09:19 AM
#1
Thread Starter
Member
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
-
Aug 22nd, 2019, 09:53 AM
#2
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
-
Aug 22nd, 2019, 10:28 AM
#3
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
-
Aug 22nd, 2019, 05:02 PM
#4
Thread Starter
Member
Re: How to convert this piece of sql code to oledb command?
Originally Posted by GaryMazzone
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.
-
Aug 22nd, 2019, 08:20 PM
#5
New Member
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 |
-
Aug 23rd, 2019, 02:04 AM
#6
Thread Starter
Member
Re: How to convert this piece of sql code to oledb command?
Originally Posted by lucaszoft
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
-
Aug 23rd, 2019, 06:45 AM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|