php - 数据库；案件;多个何时？然后 ？。如何创建数据？与 foreach

Question

具有以下值的输入行(例如，仅显示 2 行，但可能有更多行；当然值可能不同)

       entry_id | transaction_partner_name | registration_number
values
          10             name 1                   number 1
          11             name 2                   number 2

必须用值更新 mysql 表

查询可能是这样的

 UPDATE 2_1_transactionpartners SET 
 CompanyName = CASE NumberRenamed 
 WHEN ? THEN ? WHEN ? THEN ? 
 END, 
 RegistrationNumber = CASE NumberRenamed 
 WHEN ? THEN ? WHEN ? THEN ? 
 END 
 WHERE NumberRenamed in (?,?)

或

$sql = "
    UPDATE 2_1_transactionpartners SET
    CompanyName = CASE NumberRenamed
    $when_then
    END, 
    RegistrationNumber = CASE NumberRenamed
    $when_then
    END
    WHERE NumberRenamed in ($placeholders_for_number_renamed)";

现在在 foreach 中创建变量

$entry_id_for_number_renamed = array();

foreach ($_POST['entry_id'] as $i => $entry_id) {

    $for_company_name .= ( $_POST['entry_id'][$i]. ','. $_POST['transaction_partner_name'][$i]. ',' );

    $for_registration_number .= ( $_POST['entry_id'][$i]. ','. $_POST['registration_number'][$i]. ',' );

    $entry_id_for_number_renamed[] = $_POST['entry_id'][$i];

    $when_then .= 'WHEN ? THEN ? ';
    $placeholders_for_number_renamed .= '?,';

}

$for_company_name = rtrim($for_company_name, ',');
$for_company_name = explode(',', $for_company_name);

$for_registration_number = rtrim($for_registration_number, ',');
$for_registration_number = explode(',', $for_registration_number);

$insertData = array_merge( $for_company_name, $for_registration_number, $entry_id_for_number_renamed );

如果有更好(更短)的解决方案，请提出建议

Answer 1

UPDATE transactionpartners
SET transaction_partner_name = q.transaction_partner_name,
    registration_number      = q.registration_number
FROM (
    SELECT 10          AS entry_id                ,
        'new name 1'   AS transaction_partner_name,
        'new number 2' AS registration_number

    UNION

    SELECT 11          AS entry_id                ,
        'new name 3'   AS transaction_partner_name,
        'new number 4' AS registration_number
    )
    q
WHERE q.entry_id = transactionpartners.entry_id