SQLITE3 query syntax error

Question

I'm trying to update a table in my dataset. When I run the below query I get "near "FROM": syntax error:

I have tired and tired but no luck in figuring it out.

I'm using sqlite3 as a database and DB Browser to execute the query.

UPDATE movieDataset
SET budget=REPLACE(movie_info.info,',','')
FROM (
  SELECT
  movie_info.movie_id,
  movie_info.info
  FROM
  movie_info, movieDataset
  WHERE
  movieDataset.movie_id = movie_info.movie_id
  and movie_info.info_type_id=105
  )AS movie_info
  WHERE movieDataset.movie_id = movie_info.movie_id;

movieDataset column list: (movie_id integer, country, budget)

movie_info column list: (movie_id, info, movie_info_type_id)

Any suggestion???

Thank you

I didn't realise it was even a thing to have a FROM in an UPDATE. Isn't it just: UPDATE table SET column = value WHERE x = y — musefan
– musefan, Commented Aug 25, 2016 at 15:56
Sqlite doesn't support this syntax, only plain UPDATE , no FROM. — Serg
– Serg, Commented Aug 25, 2016 at 16:10

Serg · Accepted Answer · 2016-08-25 20:11:34Z

1

Try this, update rows with movie_id which has info_type_id=105 in corresponding movie_inforow

UPDATE movieDataset
SET budget=REPLACE(movie_info.info,',','')
WHERE movieDataset.movie_id IN (
  SELECT movie_info.movie_id
  FROM movie_info
  WHERE movie_info.info_type_id=105
  )

edited Aug 25, 2016 at 20:11

answered Aug 25, 2016 at 16:17

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gordon Linoff Over a year ago

This looks like a very reasonable answer to me.

sam jackson Over a year ago

Thank you serg for your help. I have tried your solution and i'm getting the same error.

sam jackson Over a year ago

Hi Serg, now i'm getting this error "no such column: movie_info.info. The info column is only in the movie_info table and not in movieDataset. I will outline all the columns in both tables in my question if that would help. Thanks again

Serg Over a year ago

Edit your question or may be ask another one regarding the problem to be solved with the query. Show sample data and the result needed.

sam jackson Over a year ago

Thank you for all your help Serg. I will write another question

Collectives™ on Stack Overflow

SQLITE3 query syntax error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related