CODEWITHSUNDEEP
mysqlsql
Stephenmelb
Stephenmelb

Reputation: 457

Insert Left Join SQL

I need help with the following SQL

I have two tables titles and postcode_db I want to LEFT JOIN and INSERT into a third table

SELECT titles.id, titles.title, titles.address, 
postcode_db.postcode, postcode_db.suburb
FROM titles
LEFT JOIN postcode_db ON titles.address = postcode_db.suburb;

Above works. I want to insert into titles_postcode

titles.id AS titles_postcode.title_id

titles.title AS titles_postcode.title

postcode_db.postcode

INSERT INTO titles_postcode 
(titles.id AS titles_postcode.title_id, 
titles.title AS titles_postcode.title, 
postcode_db.postcode AS titles_postcode.postcode)
SELECT titles.id, titles.title, titles.address, 
postcode_db.postcode, postcode_db.suburb
FROM titles
LEFT JOIN postcode_db ON titles.address = postcode_db.suburb;

Its returning error right syntax to use near 'AS titles_postcode.title_id, titles.title AS titles_postcode.title, postcode_db.' at line 1

Upvotes: 0

Views: 165

Answers (3)

Dipendu Paul
Dipendu Paul

Reputation: 2753

INSERT INTO titles_postcode(title_id,title,postcode) 
SELECT titles.id, titles.title, titles.address, 
postcode_db.postcode
FROM titles
LEFT JOIN postcode_db ON titles.address = postcode_db.suburb;

Upvotes: 1

Fabio
Fabio

Reputation: 23510

You shouldn't use alias in your insert query and also your values doesn't match column count, i asssume you only need those 3 columns to be inserted

INSERT INTO titles_postcode  (title_id, title, postcode)
SELECT titles.id, titles.title, postcode_db.postcode
FROM titles
LEFT JOIN postcode_db 
ON titles.address = postcode_db.suburb;

Upvotes: 1

xlecoustillier
xlecoustillier

Reputation: 16361

Try this:

INSERT INTO titles_postcode (title_id, title, postcode)
SELECT titles.id, titles.title, postcode_db.postcode
FROM titles
LEFT JOIN postcode_db ON titles.address = postcode_db.suburb;

Upvotes: 1

Related Questions