MariaDB : an INSERT…SELECT with empty VALUES too

I want insert data from another incomplete table, so some values must be empty in NOT NULL fields.

For example, I have two tables : people and peopleTemp.

In people table, I have a lot of fields : permit_number, first_name, last_name, gender, tel, etc. In peopleTemp table, I have only permit_number, first_name, last_name and a special field updated, set after an UPDATE query.

Now I want insert person they are’nt present in people. Gender and tel are NOT NULL, but must be empty. I try :

INSERT INTO people p1 (gender, tel, permit_number, first_name, last_name)
                VALUES ('','', (
                SELECT permit_number, first_name, last_name
                FROM peopleTemp p2
                WHERE p2.updated = 0))

But I get this error : SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

Can you help me to resolve this situation ? Thank you in advance !

Additional question : Can I SET p2.updated = 1 at the end of the query ?

Answer

You can fix your syntax error by using insert . . . select:

INSERT INTO people p1 (gender, tel, permit_number, first_name, last_name)
    SELECT '', '', permit_number, first_name, last_name
    FROM peopleTemp p2
    WHERE p2.updated = 0;

I would suggest that you use NULL instead of an empty string to represent a missing value.