如何在单条DELETE语句中操作多表（部分表无目标数据）

Hey there! Let's get that delete operation working properly. The issue with your original query is that MySQL doesn't support the comma-separated table list with a simple WHERE clause for multi-table deletes like that. Plus, if any of the variationtable tables don't have rows matching prjId = 'xxxx', the entire query might fail or skip deletions in tables that do have matching rows.

Here are two solid solutions for you:

Solution 1: Delete from Each Table Separately (Recommended) #

This approach is straightforward, reliable, and handles cases where some tables don't have matching prjId rows perfectly. Each delete runs independently, so even if one table has no matching data, the others will still process their deletions.

Basic Version #

$prjId = 'xxxx';
$tables = [
    'dbtuts.tbl_uploads',
    'dbtuts.variationtable1',
    'dbtuts.variationtable2',
    'dbtuts.variationtable3',
    'dbtuts.variationtable4',
    'dbtuts.variationtable5',
    'dbtuts.variationtable6'
];

foreach ($tables as $table) {
    $deleteQuery = "DELETE FROM $table WHERE prjId = '$prjId'";
    mysqli_query($conn, $deleteQuery);
    
    // Optional: Add error handling to debug issues
    if (mysqli_error($conn)) {
        echo "Error deleting from $table: " . mysqli_error($conn);
    }
}

Secure Version (Prevent SQL Injection) #

If $prjId comes from user input, always use prepared statements to avoid SQL injection risks:

$prjId = 'xxxx';
$tables = [
    'dbtuts.tbl_uploads',
    'dbtuts.variationtable1',
    'dbtuts.variationtable2',
    'dbtuts.variationtable3',
    'dbtuts.variationtable4',
    'dbtuts.variationtable5',
    'dbtuts.variationtable6'
];

foreach ($tables as $table) {
    // Prepare the statement
    $stmt = mysqli_prepare($conn, "DELETE FROM $table WHERE prjId = ?");
    // Bind the prjId parameter (s = string type)
    mysqli_stmt_bind_param($stmt, "s", $prjId);
    // Execute the delete
    mysqli_stmt_execute($stmt);
    // Close the statement
    mysqli_stmt_close($stmt);
}

Solution 2: Single Multi-Table DELETE Query #

If you prefer to use one query, you can use MySQL's proper multi-table DELETE syntax with LEFT JOIN to ensure tables without matching rows don't block deletions in other tables. This will delete matching rows from all tables where they exist:

DELETE tu, vt1, vt2, vt3, vt4, vt5, vt6
FROM dbtuts.tbl_uploads tu
LEFT JOIN dbtuts.variationtable1 vt1 ON vt1.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable2 vt2 ON vt2.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable3 vt3 ON vt3.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable4 vt4 ON vt4.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable5 vt5 ON vt5.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable6 vt6 ON vt6.prjId = tu.prjId
WHERE tu.prjId = 'xxxx';

In PHP, you can execute this like so:

$deleteQuery = "DELETE tu, vt1, vt2, vt3, vt4, vt5, vt6
FROM dbtuts.tbl_uploads tu
LEFT JOIN dbtuts.variationtable1 vt1 ON vt1.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable2 vt2 ON vt2.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable3 vt3 ON vt3.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable4 vt4 ON vt4.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable5 vt5 ON vt5.prjId = tu.prjId
LEFT JOIN dbtuts.variationtable6 vt6 ON vt6.prjId = tu.prjId
WHERE tu.prjId = 'xxxx'";

$resultDeleteQuery = mysqli_query($conn, $deleteQuery);

Note: This query relies on tbl_uploads having at least one matching row. If tbl_uploads has no rows with prjId = 'xxxx', no deletions will happen across any tables. If that's a scenario you need to handle, the separate delete approach is better.

内容的提问来源于stack exchange，提问作者Anu