Is there a way to concatenate two virtual columns in Oracle SQL-CodePudding

I have created a table Mailing_List using the CUSTOMERS table. I think that makes all the columns in Mailing_List table, virtual columns

I want to concatenate the Lastname & Firstname columns into one column that reads Lastname,Firstname as Name. Name will be the new column

UPDATE Mailing_List
SET || LASTNAME ',' FIRSTNAME || = Name VARCHAR2(23);

Error starting at line : 12 in command -
UPDATE Mailing_List
SET ||LASTNAME','FIRSTNAME|| = Name (VARCHAR2(23))
Error at Command Line : 13 Column : 5
Error report -
SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification
01747. 00000 - "invalid user.table.column, table.column, or column specification"
*Cause:
*Action:

ALTER Mailing_List
SET ||  LASTNAME ',' FIRSTNAME || = Name (VARCHAR2(23));

Error starting at line : 12 in command -
ALTER Mailing_List
SET ||LASTNAME','FIRSTNAME|| = Name (VARCHAR2(23)) Error report -
ORA-00940: invalid ALTER command
00940. 00000 - "invalid ALTER command"
*Cause:
*Action:

These are two different statements that I have tried, and the error messages I have received..

CodePudding user response：

You've created a table based on another. Something like this probably:

CREATE TABLE mailing_list AS SELECT * FROM customer;

This makes this a mere table (a copy of the other one). No virtual columns here.

Now you want to add a virtual column to the table, namely a column called NAME that is the concatenation of the columns LASTNAME and FIRSTNAME separated by a comma and blank. The syntax for this:

ALTER TABLE mailing_list ADD name AS (lastname || ', ' || firstname);

CodePudding user response：

You might want your mailing_list object to be a view, rather than a table. As @Thorsten said, at the moment you are creating a copy of the customers table, which means that it will not automatically reflect any changes made to the original table - changed names, added or removed customers, etc. A view is a stored query, so it will always reflect the data in the base table:

create view mailing_list as
select c.*, c.lastname || ', ' || c.firstname as name
from customers c;

It would be better to list out all of the columns you actually want instead of using c.*; even if you want them all, but you have to if you don't want to include original the firstname and lastname in the view.

db<>fiddle with the view definition above, the same thing without a table alias (following a comment), and a version with only a few columns from the original table.