Sqlite- I've got the select, how do I update these records?

Question

My select statement finds the records I want to update- Now I want to invert (multiply x -1) the adjusted_sentiment score for only these records. Here is the select statement:

Select  players.name, fbposts.company,  fbposts.post_id, reactions.reaction, 
fbposts_comments.adjusted_sentiment, fbposts_comments.message, fbposts.message from fbposts
join reactions on reactions.post_id = fbposts.post_id
join players on players.id = reactions.id
join fbposts_comments on fbposts_comments.post_id = fbposts.post_id
where adjusted_sentiment > 0 and reactions.reaction like "ANGRY" and 
reactions.id = fbposts_comments.comment_id group by fbposts.post_id

This returns records like:

Baktiyar Romanov,WorldOfWanderlust,387990201279405_994067924004960,ANGRY,0.5965,probably fed very ill-mannered rich man
Sheridan Toms,australiapost,96085205666_10153485650690667,ANGRY,0.04676666666666666,Seriously? You can't even get an express post parcel from victoria to wa in under 2 weeks!!!! Super annoyed  
Robert Smissen,australiapost,96085205666_10153487649895667,ANGRY,0.8555,Looks like Australia Post is using Diggers' letters to gain some reflected glory
Eve Ismaiel,australiapost,96085205666_10153500759100667,ANGRY,0.1133333333333333,"Ha ha... Present $20, postage $30!!!"

What I want to do is invert the adjusted_sentiment score. For example in the first record, the adjusted_sentiment score is 0.5965 I want to update that to -0.5965

BTW my queries and updates will be done via Python2.7... One thought I working on now is to create a list from the query above then use that list to create a series of update statements.

score -1 · Answer 1 · answered Feb 04 '17 at 05:04

-1

Below query will give you the expected output

update fbposts_comments set adjusted_sentiment = -adjusted_sentiment where post_id in (Select fbposts_comments.post_id from fbposts
join reactions on reactions.post_id = fbposts.post_id
join players on players.id = reactions.id
join fbposts_comments on fbposts_comments.post_id = fbposts.post_id
where adjusted_sentiment > 0 and reactions.reaction like "ANGRY" and 
reactions.id = fbposts_comments.comment_id group by fbposts.post_id)

answered Feb 04 '17 at 05:04

Chintan Udeshi

362
1
8

This did not work. I've been reading that sqlite cannot update on join statements. – Timothy Lombard Feb 04 '17 at 05:14
can you share your sample sqlite file sqlite does not support update on joins but i have a nested sub query on which i am running update query – Chintan Udeshi Feb 04 '17 at 05:14
I feel the sqlite db is too large to share (350MB) and the script to create the tables is too long to fit into comment window. looking into using a python script to solve... – Timothy Lombard Feb 04 '17 at 06:20
if possible just share just db with few records that will also work – Chintan Udeshi Feb 04 '17 at 06:24
I have tried with my sample db and i am able to do update on subquery with joins – Chintan Udeshi Feb 04 '17 at 06:31

Sqlite- I've got the select, how do I update these records?

1 Answers1