MSQL DB for WordPress site.
I would like to update a date column for each row in a table, setting the date/time to an incrementing value (e.g. increment value by 1 day). The order that I do the updates should be based on the textual sort of another column in the row. The actual date value used isn't relevant (it could start at 2001-01-01 00:00:00 and increment in seconds, minutes, hours or days).
Example of two columns in the table:
field_date , title_text
2014-08-20 09:00:27, AAA-Entry
2014-08-24 10:00:00, ZZZ-Entry
2014-08-27 10:15:00, MMM-Entry
So, the row with the first 'title_text' (alphabetical sorting) should get the newest date. The next row based on title_text should get a slightly older date and so on until the last row (based on title_text) has the oldest date. So the data shown above would end up looking something like:
field_date , title_text
2000-01-03 00:00:00, AAA-Entry
2000-01-01 00:00:00, ZZZ-Entry
2000-01-02 00:00:00, MMM-Entry
After the update then the command: select * from tbl_name order by field_date
would show output that would also be in alphabetical order of the title_text field. This would be equivalent to running: select * from tbl_name order by title_text
I'm looking at: [Incrementing datetime field with an update statement for SQL2005 and trying to get this to work on mySQL as I think this would work (I just change the 'order by' statement to 'order by title_text'). But I'm having problems with converting this to mySQL.
I'd appreciate any suggestions on different approaches or with getting the above solution to work in mySQL.
Thanks
Ian
Note: The reason behind this is that Wordpress populates list boxes/search results, based on the posting date, but I want it to be ordered based on the title text. There may be a 'Wordpress' answer (or plugin) to do this (and if you know, please feel free to tell me ;-) ), but from a learning viewpoint, I'd also like to understand how this was possible using mySQL.
In MySQL, you can do updates with sorts. The easiest way is to define a variable first and then do the update:
set @rn = -1;
update t
set field_date = date('2000-01-01') + interval (@rn := @rn + 1) day
order by title_text desc;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments