Use vim to remove ^M end of line characters

Posted by Wes in Blog      1 comment

Aug
31

Sometimes, when you open a file in vim, the end-of-line (EOL) characters are messed up. You’ll especially see this if you move a file from Windows to Unix.  You’ll see a ^M at the end of each line.

How do you remove all of these ^M characters from vim?

:%s/^M//g

The most difficult part of this is entering the control character.  You type ^V to get that, making the actual keys you press this:

:%s/<control>V<control>M//g

Breaking it down:

• :%s – search and replace using regular expressions
• ^M – the control V – control M characters
• g – global replace, ie replace all

Using SQL to delete rows from a table using INNER JOIN to another table

Posted by Wes in Database      No comments

Aug
31

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.