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.