Theekopje Theekopje - 2 years ago 66
SQL Question

Move number down if number is missing mysql

I've got a database with question numbers between 1 and 21 all with answers / values etc.
But I want to be able to delete for example question 7.
And if I delete that question that is between other numbers that the question numbers will automatically change from 1-21 to 1-20 instead of 1-6, 8-21.
Is this possible? I tried searching on the web but I couldn't find what I was looking for.

With kind regards,

How do I make this shorter? I know it can be done with innerjoin but I dont know how to correctly use it because the explanations are unclear to me..

`$sql = "DELETE FROM insertquestion
WHERE nummer='".$questionNumber."'";

$deleteFromQuestion = $db->prepare($sql);
$updateSql = "UPDATE insertquestion SET nummer = nummer - 1 WHERE nummer >= '".$questionNumber."'";

$updateSql = $db->prepare($updateSql);

and that another time but then with another table (

Answer Source

You can use Triggers to do this operation ref:

or use a update query to set the values (however this method is costly, if tthe number of rows is great in number)

Update table set q_no=q_no-1 where q_no> 5 //(if you delete a question with number 5)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download