CODEWITHSUNDEEP
sqlsql-updatederby
Aldo
Aldo

Reputation: 546

Derby db: sql update the results of a select statement

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?

Upvotes: 3

Views: 5048

Answers (2)

José Henrique
José Henrique

Reputation: 21

For record: I did it. The select clause has to return only one result per row to be updated, so:

UPDATE MY_TABLE1 A SET 
    A.FIELD1 = (SELECT b.FIELD2 
                FROM MY_OTHERTABLE B 
                WHERE (A.FIELD3= B.FIELD3) AND (A.FIELD4= B.FIELD4));

Upvotes: 2

Mark Rotteveel
Mark Rotteveel

Reputation: 109157

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')

Upvotes: 5

Related Questions