CODEWITHSUNDEEP
sql-serverview
J.Doe
J.Doe

Reputation: 93

Add a new column to a view in SQL

This is my view

CREATE VIEW seat_availability AS
SELECT flightid,flightdate, maxcapacity,
FROM flight

And I want to add 2 new columns named 'bookedseats' and 'availableseats' which don't exist in any tables but are columns I need to add.

I've done my research online and some say you can alter views by using:

ALTER VIEW

And some have said that you can't do that and have to edit from the view you've just created.

I've tried this:

CREATE VIEW seat_availability AS
SELECT flightid,flightdate, maxcapacity, bookedseats varchar(10), availableseats varchar(10)
FROM flight

which gave this error:

ERROR: syntax error at or near "varchar" LINE 2: ...ECT flightid,flightdate, maxcapacity, bookedseats varchar(10...

I've also tried ALTER VIEW:

ALTER VIEW seat_availability AS
SELECT flightid,flightdate, maxcapacity, bookedseats varchar(10), availableseats varchar(10)
FROM flight

And I got this error:

ERROR: syntax error at or near "AS" LINE 1: ALTER VIEW seat_availability AS

It would be easy to add columns if they existed in other tables but because I need to add 2 columns that don't exist in any table, it's proving difficult to do. If someone could help it would be very appreciated. Thank you.

Perhaps I may need to drop the view? and start again with two new columns added but how do I add them since they don't exist in any table in my database??

Upvotes: 8

Views: 31197

Answers (2)

Archias
Archias

Reputation: 383

Try this to add fake columns to the view:

ALTER VIEW [dbo].[view_seat_availability]
AS
SELECT    flightid
          ,flightdate
          ,maxcapacity
          ,CAST(NULL AS VARCHAR(10)) AS 'bookedseats'
          ,CAST(NULL AS VARCHAR(10)) AS 'availableseats'
FROM      flight

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

You don't define the datatype of a column like that. You let the view use the underlying datatype like this.

ALTER VIEW seat_availability AS
SELECT flightid
    , flightdate
    , maxcapacity
    , bookedseats
    , availableseats
FROM flight

Or if you need to explicitly change the datatype you need to use CONVERT like this.

ALTER VIEW seat_availability AS
SELECT flightid
    , flightdate
    , maxcapacity
    , bookedseats = convert(varchar(10), bookedseats)
    , availableseats = convert(varchar(10), availableseats)
FROM flight

Upvotes: 3

Related Questions