0
CREATE TABLE Studio
( name VARCHAR(30),
  address VARCHAR(50),
  pres VARCHAR(30),
  PRIMARY KEY (name)
);


CREATE TABLE Movie
( title VARCHAR(50),
  year NUMERIC(4),
  length NUMERIC(3),
  inColor CHAR(1),
  studioName VARCHAR(30),
  PRIMARY KEY (title, year),
  FOREIGN KEY (studioName) REFERENCES Studio(name)

MariaDB [movieindustry9]> CREATE VIEW MovieStudio AS
    -> SELECT
    -> Movie.title, Movie.year, Movie.length, Movie.studioName, Studio.address, Studio.pres
    -> FROM
    -> Movie, Studio ;
    );

MariaDB [movieindustry9]> INSERT INTO
    -> MovieStudio
    -> VALUES
    -> ('Madagascar 3', 2012, 93, 'Paramount Pictures',  'Hollywood, California, USA', 'Brad Grey') ;

ERROR 1394 (HY000): Can not insert into join view 'movieindustry9.moviestudio' without fields list

Any idea to fix it ? or can anyone explain why this happened ?

Awa
  • 15
  • 3
  • The error-message is very self-explanatory: **you need to list the columns you want to insert into**. What don't you understand about it? – Dai Nov 29 '20 at 05:31
  • Also, while inserting into a `VIEW` is a standard feature of an RDBMS, I don't think it's a good idea to *intentionally* design an application to insert into a view because doing-so places restrictions on what the VIEW can do, for example - and it will make SQL beginners scratch their heads because it's one of the (many) unexpected and surprising things ISO SQL can do (meanwhile, we **still** can't reference columnar expressions in the same `SELECT` clause, the SQL committee's priorities are all messed-up imo). – Dai Nov 29 '20 at 05:33
  • 1
    I'm not familiar with the NUMERIC data type in MariaDB. Can you point to a document that discusses that? – Strawberry Nov 29 '20 at 07:37

1 Answers1

0

First of all, you should read the error message, it tells you that you need the columns, You cannot:

INSERT INTO
    -> MovieStudio
    -> VALUES
    -> ('Madagascar 3', 2012, 93, 'Paramount Pictures',  'Hollywood, California, USA', 'Brad Grey') ;

You need:

INSERT INTO
    -> MovieStudio(Field_name, Another_one, etc)
    -> VALUES
    -> ('Madagascar 3', 2012, 93, 'Paramount Pictures',  'Hollywood, California, USA', 'Brad Grey') ;

Also Personally I wouldn't insert values into a view, create a table for that, Especailly since you are a beginner, doing such things is not a good idea..... Also you are a beginner I would avoid using the

->

Syntax for now, Where do we use the object operator "->" in PHP? It is a bit too complicated for you to understand right now....

Yunfei Chen
  • 630
  • 1
  • 8
  • 20