Search code examples
sqlsql-updatederby

SQL update from one table to another based on column match on Apache Derby

N.B. I'm looking for a solution working on Apache Derby

I have two sql tables. The first, called links, contains links of the form

SOURCE    | TARGET
1234      | 456
15        | 625
...       | ...

where the integers here are the ids of the objects being linked. The second table, called redir, contains redirection links:

ID        | REDIRTARGET
456       | 521
198       | 140
...       | ...

If the target of a link is in the id column of redir, then it must be redirected to the object of id redirtarget.

Now, I would like to update my links table by replacing all targets which are in the id column of redir by the associated redirtarget.

For example, given the two tables above (without ellipses), the update instruction would replace 456 with 521 in the target column of links.

I haven't been able to find a working instruction on my own. I've tried things like

UPDATE links,redir SET target=redirtarget WHERE id=target

but that won't compile (specifically, derby points out at the comma between UPDATE and SET). Help anybody ?

Solution

  • You can't specify multiple tables in an UPDATE list.

    If ID in the redir table is unique, you should be able do something like this:

    update links
  set target = (select redirtarget 
                from redir
                where redir.id = links.target)
where exists (select *
              from redir
              where redir.id = links.target);

    The where condition ensures that only rows in links are updated where there is actually a match in the redir table.