How do I combine two columns into another in SQLite?

Question

I currently have this trigger in MySQL. (On Table 1, EVENT: Before Insert)

set new.combinedColumn = concat(trim(new.column1), trim(new.column2))

How do I combine two columns like this, except in SQLite? I've tried multiple different ways and haven't been able to get a working statement.

This is what i have so far

BEFORE INSERT ON [Table] 
FOR EACH ROW 
BEGIN 

set Column1 = column2 || column3;

END >

i get the error

5/9/2014 2:19:34 PM: SQL Error: near "set": syntax error  <CREATE TRIGGER [insert]

This seems like it's really two questions, what are the SQLite equivalents to the MySQL functions `CONCAT` and `TRIM`; if so, there's the SO question [how can we concatenate string in sqlite - Stack Overflow](http://stackoverflow.com/questions/6134415/how-can-we-concatenate-string-in-sqlite) and there's a `trim` function in SQLite that's probably equivalent. — Kenny Evitt, May 09 '14 at 20:59
Did you read the [documentation](http://www.sqlite.org/lang_createtrigger.html)? What did you try? — CL., May 09 '14 at 21:02
Your one question isn't likely to be helpful to anyone else, as your one question is simply asking for someone to translate an effectively arbitrary expression from one SQL variant to another. — Kenny Evitt, May 09 '14 at 21:03
Scan [Query Language Understood by SQLite](http://www.sqlite.org/lang.html); I did and found enough in a few minutes for me to be able to translate your statement. — Kenny Evitt, May 09 '14 at 21:06
I've edited my original comment with my new statement, that still doesn't work. Better? — user3618577, May 09 '14 at 21:20

score 0 · Accepted Answer · answered May 10 '14 at 06:56

SQLite has no SET statement; the only statements allowed in triggers are SELECT/INSERT/UPDATE/DELETE. Therefore, you cannot affect the values to be inserted in the trigger; you have to update the table afterwards:

CREATE TRIGGER [...]
AFTER INSERT ON MyTable
FOR EACH ROW
BEGIN
    UPDATE MyTable
    SET Column1 = Column2 || Column3   -- or trim(...)
    WHERE rowid = NEW.rowid;
END;

How do I combine two columns into another in SQLite?

1 Answers1