landcross landcross - 4 years ago 125
SQL Question

SQL: how to replace foreign key column with data from referenced table?

I have 2 tables, let's call 'm Table-A and Table-B. Table-A contains a FK referencing Table-B. I don't need Table-B anymore, but I do need to keep 1 column from Table-B. So, I want to replace all foreign-keys in Table-A with the data from a single column in the referenced row in Table-B. So I'm wondering, is there a SQL query I can use to perform that action?

It's just a simple sqlite database, if that matters.

To make a bit more clear, here's an 'illustrated' example:

Current situation:

TABLE-A:
ID COL1 COL2 COL3(FK)
-------------------------------
1 text text 13
2 text text 14

TABLE-B:
ID COL4 COL5 COL6
-------------------------------
13 rice sushi pizza
14 pasta fries chips


Wanted situation:

TABLE-A:
ID COL1 COL2 COL3
-------------------------------
1 text text pizza
2 text text chips

Answer Source

Yes. It's called JOIN.

select
    a.id,
    a.col1,
    a.col2,
    b.col6 col3
from tablea a join tableb b
on a.col3 = b.id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download