CODEWITHSUNDEEP
sqlite
Narcis Petru
Narcis Petru

Reputation: 33

SQLite efficient update after select

I have two tables : Position with columns: id,x,y,entityID Speed with columns : id,speedX,speedY,entityID

Only some entities have speed so i need an inner join somehow to between Position and Speed.

My problem is i want to update position with speed from Speed table for Position.entityID = Speed.entityID in one query.

Please , can you hit me with some SQL stuff?

Thank u!

Upvotes: 1

Views: 69

Answers (1)

harsh
harsh

Reputation: 7692

A typical approach for update with join:

update Position 
  set x=(select speedx from Speed s where s.entityID=Position.entityID),
  set y=(select speedy from Speed s where s.entityID=Position.entityID)
where exists (select 1 from Speed where s.entityID=Position.entityID)

Performance vise this isn't optimal (inner queries), you may also want to evaluate "INSERT OR REPLACE" if that fits into your scenario.

Upvotes: 2

Related Questions