CODEWITHSUNDEEP
sqlmariadb
Scryptus
Scryptus

Reputation: 3

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 ?

Upvotes: 0

Views: 1345

Answers (2)

FanoFN
FanoFN

Reputation: 7114

For your additional question .. No, you can't. SET works with UPDATE.. . If you want to update the p2.updated = 1 after you done your INSERT, you have to run an additional UPDATE query on the peopleTemp table JOIN with people table for validation. Something like below should work:

... after you done INSERT query suggested by Gordon

UPDATE peopleTemp p2 
JOIN   people p1
ON     p2.permit_number=p1.permit_number 
AND    p2.first_name=p1.first_name
AND    p2.last_name=p1.last_name
SET    p2.updated=1
WHERE  P2.updated=0;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 2

Related Questions