How to update a VARCHAR value with a simple increment using MySQL?

2 Views

In order to clean a WordPress database, I’m currently attempting to update the post_name VARCHAR field with an simple incremented value.

After having tested a specific solution given by @gmoliv (I would prefer simple numbers according to selected rows), I tried to adapt this trick given by @gianluca, i.e.:

Read More
SET @rownum:=0;
UPDATE wp_posts
SET post_name = @rownum:=rownum+1
WHERE post_status="publish" AND post_type="post"
ORDER BY post_date

Unfortunately, I get an error #1054 Unknown column ‘rownum’ in ‘field list’

Thank you if anyone can enlighten me on this problem.

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. You are using the MySQL variable as a column name in your UPDATE query.

    Before:

    SET @rownum:=0;
UPDATE wp_posts
SET post_name = @rownum:=rownum+1
WHERE post_status="publish" AND post_type="post"
ORDER BY post_date

    After (see line 3):

    SET @rownum:=0;
UPDATE wp_posts
SET post_name = @rownum:=@rownum+1
WHERE post_status="publish" AND post_type="post"
ORDER BY post_date