Lepiment Lepiment - 2 months ago 7
SQL Question

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 ?

Answer

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.