SQLite EXISTS with UPDATE Statement

Åsmund Sælen 0
Viewed 32

I'm using SQLite and have two tables:

Line { Element_ID, length }

Element{ Element_ID, Name }

I want to update the parameter "length" to a certain value in Line by specifying the "Name" in Element where Element_ID is the same for Line and Element.

SQLite does not support the JOIN statement in combination with the UPDATE statement, so I've been trying various combinations of UPDATE with EXISTS without any success. Example; update length to 2 for the line with element Name 'c18':

UPDATE Line
SET length = 2
WHERE EXISTS (SELECT length FROM Line WHERE Line.Element_ID = Element.Element_ID AND Element.Name = 'c18')

Result: Error: No such column: Element.Element_ID

UPDATE Line
SET length = 2
WHERE EXISTS (SELECT length FROM Line INNER JOIN Element ON Line.Element_ID = Element.Element_ID WHERE Element.Name = 'c18')

Result: The code updates length for ALL lines (it looks like it disregards the last part in the EXISTS-statement WHERE Element.Name = 'c18'. I don't understand why this is happening, because if I only run the SELECT-statement inside the WHERE EXISTS(..) the program selects the correct line (c18).

Can anyone help me with this?

sql-update sqlite inner-join join
1 Answers

The first query doesn't work because you are referencing Element in the WHERE clause without defining it anywhere (as a table or an alias).

The second one because the subquery does not depend on the main query (it is not a correlated subquery) and therefore it has the same value for every row.

A simpler way to do this could be :

UPDATE Line SET length = 2
WHERE Element_ID in ( SELECT Element_ID FROM Element WHERE Name = 'c18' )

or to make it a bit more explicit :

UPDATE Line SET length = 2
WHERE Element_ID in ( SELECT e.Element_ID FROM Element e WHERE e.Name = 'c18' )
bwt0
Related