I can't get this SQL statement right. I want to select band emails into another table but I don't understand what I'm doing wrong:
INSERT INTO submitted (mail)
SELECT
submitted.bandid,
band.mail,
band.id
FROM
band
WHERE
submitted.bandid = band.id
Can anybody help me?
Try this, you had specified 1 column to INSERT INTO but your SELECT query contained 3 columns, also the tables in your weren't joined:
INSERT INTO submitted (mail)
SELECT
band.mail
FROM band
INNER JOIN submitted ON submitted.bandid = band.id
You haven't mentioned the error message you're getting, but I suspect the problem is that you only have one column specified in the INSERT part of the statement, but the SELECT part returns many columns.
This w3schools page has a good example of how this query should be structured:
Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
I suspect you want update, not insert:
UPDATE s
SET mail = b.mail
FROM submitted s JOIN
band b
ON s.bandid = b.id;
insert inserts new rows into the table. update updates columns in existing rows.
mail) in your new table - but you're selecting three columns from the original table - that won't work. Either define the three columns you want to insert those values into in your destination table, OR just select the one column you want to actually use to insert data from the source table