我正在处理一个更新查询,其中值仅在值不为null或为空时才应更新。现在,无论值如何,它都会更新所有内容。请帮我解决这个问题。
$query = "UPDATE bundels
SET batchkosten = CASE WHEN ". $_POST['batchkosten'] . " IS NOT NULL
THEN ". $_POST['batchkosten'] . "
ELSE batchkosten
END CASE,
CASE WHEN ". $_POST['maandelijkse_kosten'] . " IS NOT NULL
THEN ". $_POST['maandelijkse_kosten'] . "
ELSE maandelijkse_kosten
END CASE,
CASE WHEN ". $_POST['aanmeldkosten'] . " IS NOT NULL
THEN ". $_POST['aanmeldkosten'] . "
ELSE aanmeldkosten
END CASE,
CASE WHEN ". $_POST['transactiekosten'] . " IS NOT NULL
THEN ". $_POST['transactiekosten'] . "
ELSE transactiekosten
END CASE,
CASE WHEN ". $_POST['referral'] . " IS NOT NULL
THEN ". $_POST['referral'] . "
ELSE referral
END CASE,
CASE WHEN ". $_POST['actief'] . " IS NOT NULL
THEN ". $_POST['actief'] . "
ELSE actief
END CASE
WHERE bundel_id = ". $_POST['bundel_id'] . "";
$result = mysql_query($query, $db) or die ('FOUT: werkt niet');
header ("Location: vergelijker_bewerken.php");
} else {
$bundels = mysql_query("SELECT bundels.psp_id, psp.psp_id, psp_naam, bundels.bundel_id, batchkosten, maandelijkse_kosten, aanmeldkosten, transactiekosten, referral, actief from bundels
JOIN psp
ON psp.psp_id = bundels.psp_ID");
}
您可以使用 case
UPDATE bundels
SET batchkosten = case when ? is not null and length(?) > 0
then ?
else batchkosten
end,
...
您当前的查询转换为(实际上应该引发错误)
UPDATE bundels
SET batchkosten = CASE WHEN ? length(?) > 0
THEN ?
ELSE batchkosten
END
WHERE bundel_id = ?
但是改用:
SET batchkosten = CASE WHEN ? is not null and length(?) > 0
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句