Using SQL to delete rows from a table using INNER JOIN to another table
Posted by Wes in 
Oftentimes, one wants to delete some records from a table based on criteria in another table. How do you delete from one of those tables without removing the records in both table?
DELETE DeletingFromTable FROM DeletingFromTable INNER JOIN CriteriaTable ON DeletingFromTable.field_id = CriteriaTable.id WHERE CriteriaTable.criteria = "value";
The key is that you specify the name of the table to be deleted from as the SELECT. So, the JOIN and WHERE do the selection and limiting, while the DELETE does the deleting.
You’re not limited to just one table, though. If you have a many-to-many relationship (for instance, Magazines and Subscribers, joined by a Subscription) and you’re removing a Subscriber, you need to remove any potential records from the join model as well.
DELETE subscribers, subscriptions FROM subscribers INNER JOIN subscriptions ON subscribers.id = subscriptions.subscriber_id INNER JOIN magazines ON subscriptions.magazine_id = magazines.id WHERE subscribers.name='Wes';
Deleting records with a join could also be done with a LEFT JOIN and a WHERE to see if the joined table was NULL, so that you could remove records in one table that didn’t have a match (like in preparation for adding a relationship.) Example post to come.
Converting mysql databases to UTF8
UTF8 is the way to go when you’re creating a new database for an application, but how do you get your existing applications upgraded?
A :limit of Rails’ Migrations
Migrations in Ruby on Rails use the “:limit” symbol to set the maximum length of the underlying field’s data type. Take for example, the following example migration:
create_table :things do |t| t.string :name, :limit => 32 t.string :description t.timestamps end
By default, Rails will create :description as data type “varchar(255)” and :name as “varchar(32)” in a MySQL database. But did you know you can set :limit to be greater than 255?
For whatever reason, many of us have gained the impression that 255 is the longest :string can be, but that just isn’t the case. If I wanted the :description field in the example above to be greater than 255, I could just define it as follows:
t.string :description, :limit => 1024
In fact, strings (i.e. varchars) in MySQL can hold up to 65,535 bytes of data.
The opinionated nature of Ruby on Rails is a great asset in most instances, but we have to be careful not to let its opinions :limit us.
Note: I’m pretty sure Rails sets the default limit of strings to be 255 for two reasons: 1) cross database compatability, and 2) MySQL’s InnoDB (utf-8) engine can’t index varchar fields exceeding 255 characters.
Further Reading
default_scope: Use Cases, Caveats, and Work Arounds
In Ruby on Rails, named scopes are class methods used to restrict and organize the data searched for. In SQL terms, a named scope adds to the conditional (WHERE) and sorting (ORDER) sections of a query. See Railscast #108 for more information.
In Rails 2.3.x, a new type of scoping was added to the API: the default_scope. By using “default_scope”, one could restrict the data retrieved by every query without the need for extra method calls; it would just happen by “default”. If you wanted to have your data sorted in a particular manner, you could add “default_scope, :order => ‘created_at DESC’” to your model. From then on, all data retrieved would be ordered by “created_at” in a “descending” manner.
Caveats
In general, default_scope should be avoided if possible. Here are a few reasons:
- Out of site, out of mind: Because you don’t see that you are scoping your data as you query it, it’s easy to forget that it is in actuality being filtered. This can lead to a lot of head scratching until you remember the default_scope.
- default_scope is inherited: All subclasses of the original model will inherit the default scoping. This may not be the behavior you desire.
- Extra overhead: It’s one thing if you need your data sorted every single time, it’s quite another if you don’t. By using default_scope, you may be unnecessarily burdening your database.
Use Cases
Like curry, default_scope is not inherently evil (obscure Phineas and Ferb reference), and there are instances where using it makes good sense. As an example, here at Databasically, we use default_scope to limit data retrieved by one application to a subset of what is available in a table. There will never be an instance where all the available data will be required, and so we limit it by default.
Here are the two use cases:
- When only a subset of the data is ever required
- When the data must be returned in a specific order every time
Work Arounds
I highlighted the words “only” and “every” in the section on use cases to make a point: default_scope should be used with caution. The fact is, however, that it’s not an “only” and “every” world, and as such, we need work arounds. In our case, it’s by using “with_exclusive_scope” and the undocumented “unscoped” (Rails 3.x only)
with_exclusive_scope example:
Article.with_exclusive_scope { find(:all) }
unscoped example (Rails 3.x only):
Article.unscoped
Conclusion
In general, we like to keep our code as DRY as possible, but in cases like default_scope, we prefer to be more explicit. We recognize the value of default_scope, but only use it when we absolutely need to, and even then we try to think of alternative methods.
How about you? We would really like to hear from you about your experiences with default scopings. What considerations do you take into account when choosing whether or not to use default_scope?
Further Reading
Changing the mysql root password
I never can remember how to do this from the command line and the docs are just hazy enough to be a pain:
> mysqladmin -u root -p password <ENTER_NEW_PASSWORD>
And yes, that is “space-p-a-s-s-w-o-r-d” after the -p. Sending that tells mysql that you want to change the password to whatever is next. You’ll be prompted for the existing/old password:
Enter password: ********