mysql - php+mysqlupdate


I have the following mysql table data where primary key is (id,seq)

id  name  seq

Now what i have to do is delete the 1st row and reduce the values of seq in remaining rows by 1

I am using php PDO. And i have deleted the first row and then fetched the remaining rows in $result and then did following


I am getting following error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry for key 'PRIMARY'

could anyone help me on this?



-- 1 a 1 1 b 2 1 c 3 1 d 4|||while($row1 = mysql_fetch_array($result,MYSQL_ASSOC)) $dbh->exec("update tablename set seq = seq-1 where id = $row1['id'] and seq > 1 ");



I don't know if I understood your problem right, but if this while statement:

while($row1 = mysql_fetch_array($result,MYSQL_ASSOC))

Loops through rows "b", "c" and "d", the problem is that, with the first loop, the values of "seq" for b, c and d are 1, 2, 3, and then in the second loop, the values of sec for b, c, and d would be 1, 1 and to. Maybe it's the problem. I think what you should do is: 1) Delete the first row 2) Execute the query just once

$dbh->exec("update tablename set seq = seq-1 where id  = $row1['id'] and seq > 1 ");

You could take the id from the deleted row. Besides, just to make sure everything works, you should add an order by statement to your query:

$dbh->exec("UPDATE tablename SET seq = seq-1 WHERE id = $row1['id'] AND seq > 1 ORDER BY seq ASC ");

I hope it helps!




because you set primary key (id,seq) together. and basic property of primary key is would be unique and not NULL. so uniqueness of the combination of id and seq must be violated thats why u r getting below error.


People are also looking for solutions to the problem: php - building dynamic form in cakephp


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.