0

Let's say we have two tables like this:

TableA                      TableB
ID   CODE   NAME         ID  A_ID OBJECT
1    200    Name1         1   1   Object1
2    #200   Name1         2   1   Object2
3    300    Name2         3   3   Object1
4    #300   Name2         4   3   Object2

In TableA Name1 with code = 200 and Name1 with code = #200 are the same.

And the A_ID from TableB is foreign key in ID in TableA. In TableB the same A_ID appears many times. The task is to change the IDs in TableB like this - instead of the item with A_ID = 1 and CODE = 200 to change every row with the A_ID = 2:

TableB
ID OBJECT
2  Object1
2  Object2
4  Object1
4  Object2

Until now I do that in separate statements like this:

SELECT * FROM TableA WHERE CODE LIKE '#%'; # this gives me the IDs that I need to change
SELECT * FROM TableA WHERE CODE NOT LIKE '#%'; # and this gives me the other IDs

After that I do JOIN and UPDATE on TableB like this:

SELECT t1.ID,t1.CODE,t2.ID,t2.OBJECT 
FROM TableA t1
JOIN TableB t2 ON t1.ID = t2.A_ID; # to see on which IDs the record appears

UPDATE TableB SET A_ID = 2 WHERE ID = 1; # to change them one by one

I've read here that Firebird does not support JOIN in UPDATE statements. Is there a way to make this in one query?

EDIT: The main idea behind this is - Table B has to have IDs only on items with CODE that starts with # from TableA. For now it has both with and without. I did try using merge for all the records but can't understand how to get the desired IDs pumped in TableB.

MERGE INTO TableB as b
USING TableA as a
ON a.ID = b.A_ID AND CODE NOT LIKE '#%'
WHEN MATCHED THEN
# here I need a statement that updates the table with the IDs 
of the items that have code starting with '#'
Tony
  • 618
  • 12
  • 27
  • 1
    See the duplicate, the recommended way is to use [`MERGE`](https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-dml-merge) or an `UPDATE` with an `EXISTS` condition. – Mark Rotteveel Mar 13 '21 at 11:28
  • Thank you. This is the exact topic I was referencing that said I can't use `JOIN` in `UPDATE`. I did try `MERGE` with `ON t1.ID = t2.A_ID AND t1.CODE NOT LIKE '#%'` but I can't understand how should I `UPDATE` the second table with the new `IDs` in the `WHEN MATCHED THEN` part. – Tony Mar 13 '21 at 11:47
  • 1
    Based on your description, something like `merge into TableB using (select ID from TableB where ID_A = 1) src on TableB.id = src.ID when matched then update set A_ID = 2`. Otherwise, you really need to update your question with what you actually tried. Although given the conditions in your question, `update TableB set ID = 2 where ID_A = 1` would be sufficient; which leads me to think that your actual conditions might be a bit more complex. – Mark Rotteveel Mar 13 '21 at 11:53
  • 1
    How do you determine the replacement ID? Can there be multiple records with the same code? If so, how do you resolve the tie? At first glance, I would think maybe `merge into TableB using (select t1.ID as A_ID_ORIGINAL, t2.ID as A_ID_REPLACEMENT from TableA t1 inner join TableA t2 on t2.CODE = '#' || t1.CODE where t1.CODE starting with '#') src on TableB.A_ID = src.A_ID_ORIGINAL when matched then update set A_ID = src.A_ID_REPLACEMENT`. – Mark Rotteveel Mar 13 '21 at 12:38
  • With this `SELECT * FROM TableA WHERE CODE LIKE '#%';` it gets me the IDs that I need. – Tony Mar 13 '21 at 12:40
  • 1
    That is not helpful to determine the logic you need to create a single query to do it all at once. Is it possible there are (ID, CODE) : (1, 200), (2, #200), (3, 200) and (4, #200), and if so, how would you determine which ID to use as the replacement? NB, in my previous comment the condition `where t1.CODE starting with '#'` should be `where t1.CODE not starting with '#'` – Mark Rotteveel Mar 13 '21 at 12:43
  • Ids that have a code starting with # – Tony Mar 13 '21 at 12:46
  • 1
    Is it possible there are multiple records with the exact same code? And if so, which one do you choose? Given my previous comment, is such example data possible, and if so, do you replace ids 1 and 3 with id 2 or 4? – Mark Rotteveel Mar 13 '21 at 12:48
  • If you mean that #200 can have id =1 and Id = 5 then no, there are no duplicates – Tony Mar 13 '21 at 12:50
  • 1
    Ok, then I would expect the merge three comments back (with the fix mentioned two comments back), to work. – Mark Rotteveel Mar 13 '21 at 12:51

0 Answers0