CODEWITHSUNDEEP
sqlpostgresql
Alain
Alain

Reputation: 21

CREATE TABLE a third table from two tables JOIN statement

I have 2 tables and want to create a third one when ta asin_id and tb asin have the same value,

with the 

SELECT * FROM ta JOIN tb ON ta.asin_id = tb.asin;

i can have the view from the third table but i cant create it.

i already tried this:

CREATE TABLE tc AS SELECT * FROM ta JOIN tb ON ta.asin_id = tb.asin;

ERROR:  syntax error at or near "tc"
LINE 1: CREATE tc AS SELECT * FROM items JOIN bs_audiotv ON items.as...

i attached a pic from the shell here: https://photos.app.goo.gl/LLjHi2wn9WQhxXkR8

Upvotes: 0

Views: 1360

Answers (1)

ismetguzelgun
ismetguzelgun

Reputation: 1105

CREATE T is not a right syntax. You have to use CREATE TABLE TABLE_NAME AS

When you are using CTAS you should specify which columns you need one by one. If same column name exists in your tables then you will receive an error again.

CTAS

Here is you script.

CREATE TABLE TC
AS
   SELECT ITEMS.ASIN_ID,
          ITEMS.TITLE,
          ITEMS.BRAND,
          ITEMS.DESCRIPTION,
          ITEMS.CATEGORIES,
          ITEMS.SPECIFICATIONS,
          ITEMS.IMAGES,
          BS_AUDIOTV.ASIN,
          BS_AUDIOTV.LINK,
          BS_AUDIOTV.PRICE_L,
          BS_AUDIOTV.PRICE_U,
          BS_AUDIOTV.PRICE 
     FROM ITEMS JOIN BS_AUDIOTV ON ITEMS.ASIN_ID = BS_AUDIOTV.ASIN;

Upvotes: 1

Related Questions