Updating value in columns based on a column from another table

Question

I am using pandasql to update or replace values in columns using an ID from another table.

I have two tables one that I am trying to update or replace the values

Table A

id  start_destination  end_destination
=======================================
1        3431              3010
2        3521              3431
3        3010              3521

Table B

destination_id     destination_name
=======================================
      3010              NameA
      3431              NameB
      3521              NameC

I am trying to write an SQL query to create the following output

id  start_destination  end_destination
=======================================
1        NameB              NameA
2        NameC              NameB
3        NameA              NameC

I tried

update TableA 
set start_destination = TableB.destination_name
from TableB
where TableB.destination_id = TableA.start_destination

But I was getting an error

(sqlite3.OperationalError) near "from": syntax error

In the real dataset, there are more than 10 columns in Table A, which I need to keep all. Also, if there is a destination_id that cannot be matched with either start or end_destination, it should be null.

Answer 1

You may try joining table B to table A, twice:

SELECT
    a.id,
    b1.destination_name AS start_destination,
    b2.destination_name AS end_destination
FROM TableA a
LEFF JOIN TableB b1
    ON b1.destination_id = a.start_destination
LEFT JOIN TableB b2
    ON b2.destination_id = a.end_destination
ORDER BY
    a.id;