Update rows content adding values from rows in another table

Question

I've already tried all things from here: Update rows from another table with no success.

I'm using SQLite, and I'm executing the query with SQLite Database Browser.

I have 3 tables Off, Vio and Loc with data like this

Off(Aid,ox,oy):
0 0 0
1 100 100
2 200 200

Vio(Vid,Aid):
0 0
1 1
2 2
3 1

Loc(vid,x,y):
0 1 2
1 5 6
2 9 1
2 2 3
3 4 4

Aid is primary key in Off and foreign key in Vio. Vid is primary key in Vio and foreign key in Loc.

I want to update the Loc rows adding the correct ox and oy to the x and y value to get something like this as a result:

"updated" Loc(vid,x,y)
0 1 2
1 105 106
2 209 201
2 202 203
3 104 104

I've tried this:

WITH CTE AS (
SELECT Loc.x, Loc.y, Off.ox AS offx, Off.oy AS offy
FROM Loc, Vio, Off
WHERE Vio.Aid=Off.Aid AND Vio.vid=Loc.vid
)
UPDATE CTE
SET x= x + offx, y= y + offy

And this:

UPDATE Loc, Off
SET x=x+Off.ox, y=y+Off.oy
FROM (
SELECT Off.ox, Off.oy
FROM Loc, Vio, Off
WHERE Vio.Aid = Off.Aid AND Vio.vid=Loc.vid
)

Within SQLite Database Browser with no success even when the SELECT query gives me all the data I need for the update.

possible duplicate of [UPDATE JOIN SQLITE](http://stackoverflow.com/questions/19270259/update-join-sqlite) — Barmar, Mar 06 '15 at 21:47

score 1 · Answer 1 · answered Mar 06 '15 at 21:54

As mentioned in the duplicate question, you have to use a correlated subquery. Since you're updating two columns, you need to use a subquery for each of them:

UPDATE Loc
SET x = (
  SELECT L1.x + Off.ox
  FROM Loc l1
  JOIN Vio ON L1.vid = Vio.vid
  JOIN Off ON Vio.Aid = Off.Aid
  WHERE Loc.vid = l1.vid),
    y = (
  SELECT L1.y + Off.oy
  FROM Loc l1
  JOIN Vio ON L1.vid = Vio.vid
  JOIN Off ON Vio.Aid = Off.Aid
  WHERE Loc.vid = l1.vid)

DEMO

Update rows content adding values from rows in another table

1 Answers1