Derby db: sql update the results of a select statement

derby, sql, sql-update

Solution

Looking at the Derby Reference Manual on the UPDATE statement the syntax is:

{
    UPDATE table-Name [[AS] correlation-Name]
        SET column-Name = Value
        [ , column-Name = Value} ]*
        [WHERE clause] |
    UPDATE table-Name
        SET column-Name = Value
        [ , column-Name = Value ]*
        WHERE CURRENT OF
}

So no, you cannot execute a query as in your question. You would need to modify it a bit like:

UPDATE ELEMENT_ATTRIBUTES a
SET a.ATTR_NAME='new name'
WHERE a.ATTR_NAME = 'old name'
AND EXISTS (SELECT 1 
            FROM ELEMENT e 
            WHERE a.ELEMENT_ID = e.ID AND e.TYPE = 'a specific type')

Problem

I have two tables: ELEMENT: ``` ID (int. key) TYPE (String) ``` ELEMENT_ATTRIBUTES: ``` ELEMENT_ID (int) ATTR_NAME (String) ``` (I am ignoring the fields in my original tables which are not essential for this question) I would like to do something like: ``` UPDATE ELEMENT e, ELEMENT_ATTRIBUTES a SET a.ATTR_NAME='new name' WHERE e.ID = a.ELEMENT_ID AND e.TYPE = 'a specific type' AND a.ATTR_NAME = 'old name' ``` is this possible in derby sql?

Original source