Updating SQL table converting varchar to int

user_9621254 0
Viewed 47

I currently have a table new_funeralhomes as below, which has the new data i wish to add to another table:

enter image description here,

Within that table, i can easily send address,postal,phone, website and email to this table addressTest:

enter image description here.

However as you can see by the new_funeralhomes table, the city/state are text value and in the second table (addressTest) they are int values connecting to these tables:

city:

enter image description here,

province:

enter image description here.

How would i go about updating the addressTest table with the new city and province? The iid and id values match up and are the same throughout.

Im newer to this so any help would be appreciated, thank you.

sql-insert select join sql php
1 Answers

You seem to be looking for an insert ... select statement with joins. The logic is:

insert into addressTest
    (address, province, postcalcode, city, primaryphone, secondaryphoe, website)
select
    n.address
    p.id,
    n.postcal,
    c.id,
    n.phone,
    n.phone2,
    n.website
from new_funeralhomes n
inner join city c on c.name = n.city
inner join province p on p.name = n.state

Side note: it seems like the source table has less columns than the target; I adapted the column list for what seem to be the matching columns - you might need to review that so it properly matches your requirement (but the join logic, which is the heart of your question, stays the same).

user_106767160
Related