CODEWITHSUNDEEP
sqldatabasepostgresql
boxy
boxy

Reputation: 139

Insert Multiple New Rows Into Table

Assume I have following table : movies with primary key movie_id a consecutive sequences of number starting 1.

movie_id | title | year | rating
1          movie1  1988   9
2          movie2  1970   8
3          moive3  2004   5
4          moive4  1999   7
5          moive5  2015   6

My goal is to create sequel for movie released before year 1990 at year 2020, the updated table would look like something :

movie_id  |       title       | year | rating
1          movie1               1988    9
2          movie2               1970    8
3          moive3               2004    5
4          moive4               1999    7
5          moive5               2015    6
6          moive1: the sequel   2020    9
7          movie2: the sequel   2020    8

I know how to get subsquery : movie released before 1990:

SELECT *
FROM movies
WHERE year < 1990;

But don't how to move on with this.

Here is my table schema:

CREATE TABLE movies (
  movie_id INTEGER PRIMARY KEY,
  title VARCHAR NOT NULL,
  year INTEGER NOT NULL,
  rating FLOAT NOT NULL,
  UNIQUE (title,year)) ;

Upvotes: 0

Views: 69

Answers (1)

Rigel1121
Rigel1121

Reputation: 2016

Use INSERT...SELECT Method. Then concatenate the string ":the sequel" in title, Lastly, Add the '2020' string in year. See below:

INSERT INTO movies
SELECT 
      title || ': the sequel' AS title,
      '2020' AS year,
      rating 
FROM movies
WHERE year<1990

Upvotes: 1

Related Questions