How to insert values into a table from the result of a SELECT statement SQL-CodePudding

I've got two tables, one called PLAYERS with this structure:

PLAYERS (name, team, goals, cod)

and another one called NEW_PLAYERS with this structure:

NEW_PLAYERS (name, team, goals)

I want to insert values from NEW_PLAYERS into PLAYERS setting the value 1 for the register cod of all of NEW_PLAYERS.

I thought this code could work but it doesn't.

INSERT INTO PLAYERS (name, team, goals, cod) 
VALUES ((SELECT name, team, goals FROM NEW_PLAYERS), 1);

Does anyone have a better option?

CodePudding user response：

You were almost there. Try this.

INSERT INTO PLAYERS(name,team,goals,cod) 
SELECT name,team,goals,1 FROM NEW_PLAYERS

CodePudding user response：

use INSERT ... SELECT not INSERT ... VALUES

INSERT INTO PLAYERS(name,team,goals,cod)
SELECT n.name
     , n.team
     , n.goals
     , 1 AS cod
  FROM NEW_PLAYERS n
 ORDER BY 1,2,3