SQLite: Update field with result of another query

Question

I have a database with one table, let's say 'tablename'. Now, I want the first column of that table, named 'text' to be updated with the text it containts PLUS some new text which comes as result from another query executed over 'tablename2'. So, I want something like this:

UPDATE tablename SET text="current text" + (SELECT * FROM tablename2 where ID=12);

If 'text' value is 'Result not available' I want to append ' here', so that the field value is 'Result not available here'

How is this possible? Thanks

see this [Update query in Sqlite](https://stackoverflow.com/a/22956377/1718223) answer , solved my problem — abbasalim, Nov 24 '17 at 15:18

score 1 · Answer 1 · answered Jul 18 '12 at 20:34

1

Try the concatenation operator (||):

UPDATE tablename SET text='current text'||(SELECT * FROM tablename2 where ID=12);

And I'm not sure, but you should use ' instead of " .

answered Jul 18 '12 at 20:34

Andrei Micu

3,159
1
17
20

You're definitely correct about the quote character and the concatenation operator, but the rest of the SQL smelly fishy. (Not that that's your fault though.) – Donal Fellows Jul 18 '12 at 21:17
' only a single result allowed for a SELECT that is part of an expression' – abbasalim Nov 24 '17 at 15:15

score 1 · Answer 2 · answered Jul 18 '12 at 23:12

1

I probably misunderstood your question, but let's try:

http://sqlfiddle.com/#!5/959e5/2

UPDATE Table1
SET text = text || 
    CASE
      WHEN text = 'Result not available' THEN ' here'
      ELSE (SELECT text FROM Table2 where id = 12)
    END;

answered Jul 18 '12 at 23:12

biziclop

14,466
3
49
65

SQLite: Update field with result of another query

2 Answers2