1

I'm trying to insert data into a table that already exists, but I cant find anything on how to do this. I only found how to insert this data into a new table.

Syntax error at or near Insert

Tutorial I visited

 SELECT film_category.film_id, film_category.category_id, rental_duration, rental_rate
 INSERT INTO category_description
 FROM film_category
 LEFT JOIN FILM
 ON film_category.film_id = film.film_id
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stack Ty
  • 51
  • 7
  • 3
    INSERT INTO category_description (film_id,category_id,rental_duration,rental_rate) SELECT film_category.film_id,film_category.category_id,rental_duration,rental_rate FROM film_category LEFT JOIN FILM ON film_category.film_id = film.film_id – Sergey Jan 30 '22 at 18:40
  • Looks like you are looking for Update? – Jens Jan 30 '22 at 18:41
  • 1
    You don't need the SELECT to just insert data (if you want to modify existing data in the table, then you need an UPDATE). – Katharine Osborne Jan 30 '22 at 18:43
  • Can you point me into the right direction? the table I'm trying to insert into is empty? – Stack Ty Jan 30 '22 at 18:57

2 Answers2

1

A simplified test to showcase methods to insert.

CREATE TABLE TableA (
ID INT GENERATED ALWAYS AS IDENTITY,
ColA1 INT, 
ColA2 VARCHAR(30)
);

--
-- INSERT VALUES into existing table
--
INSERT INTO TableA (ColA1, ColA2) VALUES
(10, 'A'),
(20, 'B'), 
(30, 'C');

3 rows affected

--
-- SELECT INTO new table
--
SELECT ID, ColA1+2 AS ColB1, ColA2||'2' AS ColB2 
INTO TableB
FROM TableA;

3 rows affected

--
-- INSERT from SELECT with explicit columns
--
INSERT INTO TableA (ColA1, ColA2) 
SELECT ColB1+1, CONCAT(LEFT(ColB2,1),'3') AS ColB23
FROM TableB;

3 rows affected

SELECT * FROM TableA;

id | cola1 | cola2
-: | ----: | :----
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | C    
 4 |    13 | A3   
 5 |    23 | B3   
 6 |    33 | C3   

--
-- INSERT from SELECT without columns
-- Only works when they have the same number of columns. 
--
INSERT INTO TableB
SELECT *
FROM TableA;

6 rows affected

SELECT * FROM TableB;

id | colb1 | colb2
-: | ----: | :----
 1 |    12 | A2   
 2 |    22 | B2   
 3 |    32 | C2   
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | C    
 4 |    13 | A3   
 5 |    23 | B3   
 6 |    33 | C3

db<>fiddle here

LukStorms
  • 28,916
  • 5
  • 31
  • 45
0

The order is wrong https://www.w3schools.com/sql/sql_insert_into_select.asp

Also see this answer Insert into ... values ( SELECT ... FROM ... )

 INSERT INTO category_description
 SELECT 
   film_category.film_id,
   film_category.category_id,
   rental_duration,
   rental_rate
 FROM 
   film_category
   LEFT JOIN FILM ON film_category.film_id = film.film_id
Johnny Saldana
  • 44
  • 4