I want to make a single query to update and select columns, like

UPDATE dbo.employees SET otp = $OTP
WHERE identification = $identity (SELECT mail,emp.id, emp.name FROM dbo.emails email
LEFT JOIN dbo.employees emp ON emp.id = email.employee 
WHERE emp.identification = $identity)

How do I get the selected column values

CodePudding user response：

As mentioned, you can use the OUTPUT clause to achieve this.

You have a couple more issues with your code:

• You must parameterize your statements, or you leave yourself open to dangerous SQL injection.
• You don't need to loop the result if you only expect one row.
• A LEFT JOIN followed by WHERE on one of the joined table's columns makes it an implicit INNER JOIN.

$OTP = generateOTP();
$sql = "
UPDATE emp
SET otp = @OTP
OUTPUT email.mail, inserted.name
FROM dbo.employees emp
INNER JOIN dbo.emails email ON emp.id = email.employee
WHERE emp.identification = @identity;
";
$stmt = $con->prepare($sql);
$stmt->bindParam('@identity', $identity);
$stmt->bindParam('@OTP', $OTP);
if($stmt->execute()) {
    $row = stsmt->fetch();
    $emailTo = $row['mail'];
    $name = $row['name'];
} else {
    // deal with errors
}

This code only works where emails is in a one-to-one relationship with employees. Otherwise you must use a separate SELECT.

CodePudding user response：

you can use store procedure

create procedure spupdate_employees_mail
@OTP int,
@IDENT varchar(20)
AS BEGIN

UPDATE employees SET OTP=@OTP WHERE identification=@IDENT

SELECT mail,employees.id,employees.name from  emails
LEFT JOIN employees ON employees.id=emails.id
WHERE employees.identification=@IDENT
END

EXECUTE spupdate_employees_mail 1001,'mechanic'  

spupdate_employees_mail take two parameter (otp as int and identification as varchar).It will update the table- employess and then fetch data from employees and emalis table on LEFT JOIN