Java MySQL UPDATE 語法錯誤：多表更新正確寫法

錯誤代碼

// 錯誤 1: UPDATE 後跟 FROM (MySQL 不支持)
sqls.add("update from user set loginName='...' where loginName='...'");

// 錯誤 2: 多表更新語法錯誤
sqls.add("update from userinfo set loginName='...', gender='...' where loginName='...'");

報錯：You have an error in your SQL syntax near 'from userinfo...'

MySQL UPDATE 標準語法

-- 單表更新
UPDATE table_name
SET col1 = val1, col2 = val2
WHERE condition;

-- 多表更新 (關聯更新)
UPDATE table1 t1
JOIN table2 t2 ON t1.id = t2.id
SET t1.col = val1, t2.col = val2
WHERE t1.condition;

修正後的代碼

// 單表 user 更新
String sql1 = "UPDATE user " +
              "SET loginName = ?, userName = ? " +
              "WHERE loginName = ?";

// 多表關聯更新 (若 user 和 userinfo 通過 loginName 關聯)
String sql2 = "UPDATE user u " +
              "JOIN userinfo ui ON u.loginName = ui.loginName " +
              "SET u.loginName = ?, u.userName = ?, " +
              "    ui.loginName = ?, ui.gender = ?, ui.birthday = ?, " +
              "    ui.address = ?, ui.mobile = ?, ui.tel = ?, ui.email = ?, " +
              "    ui.quanpin = ?, ui.jianpin = ? " +
              "WHERE u.loginName = ?";

參數化預編譯 (防注入、類型安全)

try (PreparedStatement ps = conn.prepareStatement(sql2)) {
    ps.setString(1, uloginName);   // u.loginName
    ps.setString(2, userName);     // u.userName
    ps.setString(3, uloginName);   // ui.loginName
    ps.setString(4, gender);
    ps.setString(5, birthday);
    ps.setString(6, address);
    ps.setString(7, mobile);
    ps.setString(8, tel);
    ps.setString(9, email);
    ps.setString(10, CHNUtil.getFullSpell(userName));
    ps.setString(11, CHNUtil.getFirstSpell(userName));
    ps.setString(12, loginName);   // WHERE u.loginName
    ps.executeUpdate();
}

關鍵錯誤對比表

事務控制 (保證兩表一致性)

conn.setAutoCommit(false);
try {
    ps1.executeUpdate(); // user
    ps2.executeUpdate(); // userinfo
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
} finally {
    conn.setAutoCommit(true);
}

總結

MySQL UPDATE **不寫 FROM**，單表直接 UPDATE table SET ...，多表用 JOIN。**務必使用 PreparedStatement 參數化**，並用事務保證多表更新原子性。