Updating column so that it contains the row position

by

This should work

update 
content,
(
  select 
  @row_number:=ifnull(@row_number, 0)+1 as new_position,
  ContentID 
  from content
  where CategoryID=1
  order by position
) as table_position
set position=table_position.new_position
where table_position.ContentID=content.ContentID;

But I would prefer to apply this first, to unset user defined variable

set @row_number:=0;

Added by Mchl:

You can do that in one statement like this

update 
content,
(
  select 
  @row_number:=ifnull(@row_number, 0)+1 as new_position,
  ContentID 
  from content
  where CategoryID=1
  order by position
) as table_position,
(
  select @row_number:=0
) as rowNumberInit
set position=table_position.new_position
where table_position.ContentID=content.ContentID;

More Related Contents:

  1. Order results from two queries based on time in second query [closed]
  2. Query database then email posts from where user is subbed to
  3. Using PHP data in MySQL [closed]
  4. Save the data into a database and return another value from database using PHP
  5. Checking the availability of rooms
  6. How can I do ‘insert if not exists’ in MySQL?
  7. How to properly set up a PDO connection
  8. Getting raw SQL query string from PDO prepared statements
  9. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  10. MySQL Prepared statements with a variable size variable list
  11. Loading .sql files from within PHP
  12. PDO with INSERT INTO through prepared statements [closed]
  13. MySQL “NOT IN” query 3 tables
  14. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  15. PHP: multiple SQL queries in one mysql_query statement
  16. Doing a while / loop to get 10 random results
  17. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  18. Why does this return Resource id #2? [duplicate]
  19. Simple PHP SQL login troubleshooting
  20. Change date format (in DB or output) to dd/mm/yyyy – PHP MySQL
  21. MySql PHP select count of distinct values from comma separated data (tags)
  22. How to SELECT DEFAULT value of a field
  23. Laravel Eloquent get results grouped by days
  24. How to check if a value already exists to avoid duplicates?
  25. how do I get month from date in mysql
  26. php/mysql zip code proximity search
  27. MYSQL select a piece of a string and order by that piece
  28. Aggregated query without GROUP BY
  29. PHP/MySQL Pagination
  30. Limit pagination page number

Leave a Comment