Delete from joined MySQL tables
When you need to delete rows from a table, and the delete conditions require a joined table, MySQL needs to know which table you want to delete from.
Let's say that Post belongs_to :author. In order to delete all posts from the author named "Eric", write
DELETE posts FROM posts LEFT JOIN authors ON posts.author_id = authors.id WHERE authors.name = 'Eric'
Notice the additional "posts" between DELETE and FROM.
Related cards:
Why preloading associations "randomly" uses joined tables or multiple queries
ActiveRecord gives you the :include option to load records and their associations in a fixed number of queries. This is called preloading or eager loading associations. By preloading association...
Using multiple MySQL versions on the same linux machine using docker
We had a card that described how to install multiple mysql versions using mysql-sandbox. Nowadays with the wide adoption of docker it might be easier to u...
uninitialized constant MysqlCompat::MysqlRes (NameError)
If you get a stacktrace complaining about uninitialized constant MysqlCompat::MysqlRes a system library update might broke your gem.
You might have [switched from MySQL to MariaDB](https://makandracards.com/makandra/51171-running-rails-2-apps-w...
Upgrading Ruby from 1.8.7 to 2.3.5
Suggested Workflow
Set the ruby version in .ruby-version to 2.3.5, then perform these steps one by one, fixing errors as they occur:
- Update gems as listed below, and bundle
- Boot a Rails console - see below for a list of changes you ...
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:
+-----...
Show the character set and the collation of your MySQL tables
To show the collation of your tables you have to login to the MySQL console and execute SHOW TABLE STATUS FROM database;
mysql> SHOW TABLE STATUS FROM test;
+-------------+--------+---------+------------+------+----------------+--------...
Installing multiple MySQL versions on the same Linux with mysql-sandbox
Ubuntu has a package mysql-sandbox that lets you install multiple MySQL versions into your user home:
- Install
mysql-sandbox
sudo apt install mysql-sandbox
- Download the version of MySQL you want to use from mysql.com:
<https://...
PostgreSQL: How to UPDATE multiple attributes with multiple joins
This is an extension to PostgreSQL vs MySQL: How to UPDATE using a JOIN.
UPDATE employees
SET department_name = departments.name,
department_area = areas.name
FROM departments, areas
WHERE employees.department_id = depar...
PostgreSQL: How to add/remove/modify array values (and how to replace 1 value with multiple values)
PostgreSQL's array data type is pretty useful, but manipulating values of arrays can be awkward because of its syntax.
Consider the following users table which each example below will sta...
Upgrading Rails 2 from 2.3.8 through 2.3.18 to Rails LTS
This card shows how to upgrade a Rails 2 application from Rails 2.3.8 through every single patch level up to 2.3.18, and then, hopefully, Rails LTS.
2.3.8 to 2.3.9
This release has [many minor changes and fi...