Sql update_all with string concatenate

rubyonrails-talk
1

From a migration. I want to initialize a new column called "ident" with a string equal to "prec-#{id}" - where id is meant to be the id column of the relevant row. This works:

    Precinct.update_all("ident = 'prec-' || id ")

I am confused about whether that bit of SQL is being parsed by Rails code, or whether it's given to Sqlite, in which case I am writing a database-dependent bit of code.

Thanks for your assistance!

Pito

2

From a migration. I want to initialize a new column called "ident" with a string equal to "prec-#{id}" - where id is meant to be the id column of the relevant row. This works:

   Precinct.update_all("ident = 'prec-' || id ")

I am confused about whether that bit of SQL is being parsed by Rails code, or whether it's given to Sqlite, in which case I am writing a database-dependent bit of code.

I believe it's going to the database, but I haven't verified it.

Follow the source though to make sure...

first line of update_all is: sql = "UPDATE #{quoted_table_name} SET #{sanitize_sql_for_assignment(updates)} " So, worth looking at sanatize_sql_for_assignment to see what it might be doing.

-philip

3

Philip Hallstrom wrote:

From a migration. I want to initialize a new column called "ident"
with a string equal to "prec-#{id}" - where id is meant to be the id column of the relevant row. This works:

   Precinct.update_all("ident = 'prec-' || id ")

I am confused about whether that bit of SQL is being parsed by Rails code, or whether it's given to Sqlite, in which case I am writing a database-dependent bit of code.

I believe it's going to the database, but I haven't verified it.

It would have to be going to the DB, or else || wouldn't have its SQL sense of concatenation.

To the OP: if this column is just going to be the id with a constant prefix, then you don't need it in the DB!

Follow the source though to make sure...

first line of update_all is: sql = "UPDATE #{quoted_table_name} SET #{sanitize_sql_for_assignment(updates)} " So, worth looking at sanatize_sql_for_assignment to see what it might be doing.

-philip

Best,

4

Marnen Laibow-Koser wrote:

To the OP: if this column is just going to be the id with a constant prefix, then you don't need it in the DB!

It's initializing existing records in a migration. Over time the match will not be present.

To the other point about "||" meaning concatenation. Thats what got me thinking as some other SQLs use "+" and others use CONCAT().

5

treats || as OR. Unless you start it up in 'ansi' mode.

Might not matter a bit for you, but if it does, you could always do the concatenation in rails via find/save/update_attribute. Slower, but would work.

Here's something else that just got me as I don't use Sqlite much...

   named_scope :approved, :conditions => "is_approved = true"

Will fail on sqlite as it doesn't recognize true.

   named_scope :approved, :conditions => ["is_approved = ?", true]

works though. I do the thing for everything else but figured 'true' was safe. Guess not.

-philip