How to update a MySQL column with ascending numbers
Given the problem you have a new column postion and that column should be updated for all existing rows with ascending numbers. Furthermore these numbers should be generated by a special order. In order to achieve that you could do the following:
execute "SET @pos := 0;"
update " UPDATE pages SET position = ( SELECT @pos := @pos + 1 ) ORDER BY updated_at DESC;"
Related cards:
MySQL: How to create columns like "bigint" or "longtext" in Rails migrations, and what :limit means for column migrations
Rails understands a :limit options when you create columns in a migration. Its meaning depends on the column type, and sometimes the supplied value.
[The documentation](http://apidock.com/rails/ActiveRecord/ConnectionAdapters/TableDefinition/co...
PostgreSQL vs MySQL: How to UPDATE using a JOIN
When you want to UPDATE a table with information from an associated table, you can JOIN the associated table into the statement.
Example
Let's say you have a database schema where an Employee belongs_to :department:
+-----...
How to create a user with all privileges to MariaDB
Create a user without password (recommended)
Replace newuser with your desired username:
mysql -uroot -p
CREATE USER 'newuser'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
exit;...
How to write complex migrations in Rails
Rails gives you migrations to change your database schema with simple commands like add_column or update.
Unfortunately these commands are simply not expressive enough to handle complex cases.
...
How DECIMAL columns deal with numbers exceeding their precision or scale
When storing floating-point numbers such as prices or totals in an SQL database, always use a DECIMAL column. Never use FLOAT or kittens will die.
DECIMAL columns are parametrized with a precision and a scale. These parameters describe whic...
How to load only a subset of a massive MySQL dump
I had a huge MySQL dump that took forever (as in: days) to import, while I actually just wanted to have the full database structure with some data to use on my development machine.
After trying several suggestions on how to speed up slow MySQL du...
How to make changes to a Ruby gem (as a Rails developer)
At makandra, we've built a few gems over the years. Some of these are quite popular: spreewald (> 1M downloads), active_type (> 1M downloads), and geordi (> 200k downloads)
Developing a Ruby gem is different from developing Rails applications, w...
How to update a single gem conservatively
The problem
Calling bundle update GEMNAME will update a lot more gems than you think. E.g. when you do this:
bundle update cucumber-rails
... you might think this will only update cucumber-rails. But it actually **updates cucumber...
PostgreSQL vs. Rails migration: How to change columns from string to integer
When writing Rails migrations to convert a string column to an integer you'd usually say:
change_column :table_name, :column_name, :integer
However, PostgreSQL will complain:
PG::DatatypeMismatch: ERROR: column "column_name" cannot...
How to load an SQL dump from a migration
If you want to load an SQL dump from an ActiveRecord migration, you might find this to be harder than you thought. While you can call ActiveRecord::Base.connection.execute(sql) to execute arbitrary SQL commands, the MySQL connection is configure...