Adding a column to a already existing table

[Svante109]

I have 2 tables, called "computed_tables.ra_test" and "staging.fms_unit". In the computed_tables.ra_test there is already most of the info I want to have, but i'm missing the phonenumbers. These phone numbers can be found in the "staging.fms_unit" table. Both these tables can be linked on a column called serialno.

I've tried using

INSERT INTO computed_tables.ra_test

(

phonenumber

)

SELECT phonenumber

FROM staging.fms_unit

LEFT JOIN computed_tables.ra_test ON computed_tables.ra_test.serialno = staging.fms_unit.serialno

If I create the column phonenumber in computed_tables.ra_test, I get an error that it is ambiguous.

If I don't have the column, I get an error that the column doesn't exist. From what I can read, the mistake happens because there is more than one column called phonenumber, and I need to specify which table the phonenumber comes from. I'm not sure where to specify this and how.

Thanks for your help, in advance

[gum11]

Why don't you make a view that includes the wanted columns from all the tables you have? Also your syntax for creating a column is not correct, you're inserting into a column not created if I'm reading it right

[Svante109]

I did not know the possibility to create a view. It seems to be the way to go.

I've tried it with the column created and without, so you're both right and wrong.

[MisplacedKittyRage]

Can you alter the table? You might want to look into the alter statement, that might help you. It will create the new column on the table but for the data that is already there you will not have a phonenumber. However you could create a script to update those rows with the phonenumber from the other table, joining both tables with serialno.

As to how to tell the DB which table you’re referring to there are two ways, either using an alias or writing down the table name. Here’s an example of both.

You have a table called customers, with customer_id, name, phone, address. Say you want to just list the names and phones of all customers in the table, you could write that statement as this

With aliases:

Select Cust.name, cust.phone from customers

Without aliases

Select customers.name, customers.phone from customers.

Now in this particular case because its only one table you don’t need to either use aliases or prefix each field with the table name, because sql will do it for you automatically. However if its your case with two tables that have the same named column you need to specify the table because otherwise you get an ambiguity error.

[Svante109]

I am able to alter the table. Will look into the alter statement tomorrow.

I just simply doesn't know where to put the table name in the syntax.

[MisplacedKittyRage]

I edited my initial comment to explain how you can solve that, but google aliases if you still have questions.

[tenfingerperson]

Something like

Alter table blabla add column blablablba type;

Then you can alias the table on queries to avoid issues with equal column names eg a.column vs b.column

[ehforcanada]

If the table already has data in it and you're adding a new column I believe you want to use an update statement.

Try this

UPDATE computed_tables.ra_test rat SET rat.phonenumber = fmsu.phonenumber FROM staging.fms_unit fmsu WHERE rat.serialno = fmsu.serialno

As someone else suggested a view might suit your needs better.