Is there an SQL INSERT INTO query that allows me to insert multiple rows from subquery plus predeter-CodePudding

I've got a class exercise in which I have to INSERT a new teacher to the teacher's table. Teacher table has:

-School Code (COD_CENTRO) references Schools Table -Teacher ID (DNI) primary key -Surname (APELLIDOS) -Department (ESPECIALIDAD)

As a condition, the School Code used must be those in which there are only 1 staff designed as Administrative. From that I get two different School Codes.

Also, I have to add the Teacher Id, Surname and Department, and those are determined in the exercise text (8790055 as ID, 'Clara Salas' as Surname and 'IDIOMAS' as Department).

What I get from this and the exercise description is that I have to INSERT two new rows in the Teachers table, which will have the same information for the teacher (ID, Surname, Department) but different School Code.

As of now I'm using a workspace in Oracle APEX, and I wrote this statement:

INSERT INTO PROFESORES (COD_CENTRO, DNI, APELLIDOS, ESPECIALIDAD)
VALUES (
    (SELECT COD_CENTRO
    FROM PERSONAL
    WHERE FUNCION LIKE 'ADMINISTRATIVO'
    GROUP BY COD_CENTRO
    HAVING COUNT(FUNCION) = 1),

    8790055, 'Clara Salas', 'IDIOMAS');

The subquery works perfectly, as it returns two values that are consistent with the data input in the table. The problem is that I get this error back:

ORA-01427: single-row subquery returns more than one row

After a little investigation, I've understood that this is because INSERT INTO...VALUES can only INSERT a single row, and my subquery returns two values, hence the error.

My question is: Is there any way that I can INSERT two new rows, using both codes, and the same teacher information for both rows in a single statement? Or maybe another way, even though it isn't in a single statement?

I've thought about creating first the two rows with both codes, and then adding the 'static' data to both rows, but I couldn't come with a way of identifying both rows in order to add my data (maybe with IS NULL?); also there's the fact that I can't create any row without adding a primary key, which is another error I don't know how to overcome.

Another idea I had was to create two INSERT INTO...VALUES, and in each one I could use one of both values by maybe adding a code that allowed me to choose only one row from the subquery, and the other one in the other statement.

I've been looping this in my head for a couple hours, and since I'm quite new I can see my limitations; for that I would love some help to solve this :)

CodePudding user response：

You can try below query -

INSERT INTO PROFESORES (COD_CENTRO, DNI, APELLIDOS, ESPECIALIDAD)
    SELECT COD_CENTRO, 8790055, 'Clara Salas', 'IDIOMAS'
      FROM PERSONAL
     WHERE FUNCION LIKE 'ADMINISTRATIVO'
     GROUP BY COD_CENTRO
    HAVING COUNT(FUNCION) = 1;