How to UPDATE a table using data from another row

The Problem: I have a table with name, num, and diff. The unusual case here is that the diff must be updated with the difference between two sibling nums for the same name. So I'll need to know the num of the next row (for the same name) to update the current one. AND I want to do that in SQL with a single UPDATE.

Here I'll assume the rows must be sorted by num, but it would be sorted by another field, like id or timestamp.

The solution in MySQL is to use a inline temporary table to get the num of next row and associate it with current id, and use that table in the UPDATE statement.

Here is the code. Enjoy!
 

-- the table used for test (MySQL syntax)
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `num` int(11) NOT NULL,
  `diff` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

-- some values to play on
INSERT INTO `test` (`id`, `name`, `num`, `diff`) VALUES
(1, 'a', 10, NULL),
(2, 'b', 8, NULL),
(3, 'a', 18, NULL),
(4, 'a', 21, NULL),
(5, 'b', 14, NULL),
(6, 'a', 32, NULL),
(7, 'b', 20, NULL),
(8, 'b', 21, NULL);

-- a select to test the ability to retrieve the desired diff value
select id, name, num, (select min(num) from test where name = t1.name and num > t1.num)-num
from test t1
where diff is null
order by name, num;

-- updating test with the calculated diff using another row on same table.
update test t2, (
  select id, (select min(num) from test where name = t1.name and num > t1.num)-num as diff
  from test t1
) t3
set t2.diff = t3.diff
where t2.id = t3.id
and t2.diff is null 

See this post in portuguese.

Capitalized function names

You can define functions with capitalized names:

#!/usr/bin/ruby

def Foo
  puts 'foo'
end

def Bar x
  puts x
end

At call time, to avoid Ruby to interpret them as constants, you must make clear that you are using them as functions, by using parenthesis or parameters:

Foo()      #=> 'foo'
Bar 3      #=> 3
Foo        #=> Error: not initialized constant

Sequel uses this feature to define methods named String, Integer, etc. for creating/altering tables.

How to install sqlite3-ruby gem on linux

If you is having trouble to install the sqlite3-ruby gem, try the following:

In systems with apt-get:

# apt-get install libsqlite3-dev

In systems with yum:

# yum install sqlite-devel

How to install bson_ext gem on linux

MongoDB requires the bson_ext gem to increase performance. However, it's common to be not ready to install it.

In ubuntu (10.04 32bit) I had to run:

# apt-get install ruby1.8-dev

In systems with yum I had to run:

# yum install gcc
# yum install make
# yum install ruby-devel


Finally in both systems I could run, with no errors:

# gem install bson_ext

A little bit on require behavior

require filename will:

• return true when it finds filename;
• return false when it already loaded filename;
• raise LoadError when it doesn't find filename.

That was not properly documented; the official documentation lean us to guess it would return false when the file is not found, and that's not the case.

Changing aliased method does not alter the original one

Changing aliased method does not alter the original one, and vice-versa.

So, if you need to alter some method that you know it's aliased, you may stay unworried: you won't affect the other aliased methods, and you can use them if you need the original behavior.

See my tests and the results below.

#!/usr/bin/ruby

class A
  def original_method
    puts "original content"
  end
  alias aliased_method original_method
  alias_method :alias_methoded_method, :original_method
end

class B < A
  def original_method
    puts "modified content"
  end
end

class C < A
  def aliased_method
    puts "modified content"
  end
end

class D < A
  def alias_methoded_method
    puts "modified content"
  end
end

[A, B, C, D].each do |klass|
  puts "#{klass}:"
  obj = klass.new
  [:original_method, :aliased_method, :alias_methoded_method].each do |meth|
    print "#{meth}: "
    obj.send meth
  end
  puts
end

The results:

A:
original_method: original content
aliased_method: original content
alias_methoded_method: original content

B:
original_method: modified content
aliased_method: original content
alias_methoded_method: original content

C:
original_method: original content
aliased_method: modified content
alias_methoded_method: original content

D:
original_method: original content
aliased_method: original content
alias_methoded_method: modified content

RewriteRule running twice

Sometimes it seems that RewriteRule is running twice, even we use the [L] flag.

The truth is: it really runs twice (or even more times)! But only when URL is changed.

The [L] flag stops the running of rules following it, but if URL is changed, the new URL will be parsed again from the beginning.

There are many solutions to that (e.g., by using RewriteCond), but the one I used is to put, as first rule, one to tell the RewriteEngine to do nothing if the URL is what I want:


RewriteEngine on

# index is the last rule - is what I want, so doesn't change anything
# and go to it (thank's to [L])!
RewriteRule ^index.php$ - [L,QSA]

# get user id - URL changed, so [L] will cause the new URL to be reparsed
# - and so it will be matched on the above rule.
RewriteRule ^user/(.*)$ index.php?user=$1 [L,QSA]

# in case of user/..., following rules don't apply,
# since the above rule has [L]
# ...

See this post in Portuguese.