CODEWITHSUNDEEP
mysql
Plummer
Plummer

Reputation: 6688

mysql - INNER JOIN - ERROR 1241 (21000): Operand should contain 1 column(s)

I have a query, let's call him Steve.

Steve is supposed insert rows containing data from six columns located in table-1 into table-2, but only pick the rows from table-1 that match values from a column in table-3... AND, if Steve finds any duplicate entries, just update the values in table-2 with the values from table-1.

INSERT INTO nht_mlslookup (MSTLISTBRD,MSTMLSNO,MSTACTSTA,MSTSTRNO,MSTSTRNAM,MSTUNITNUM) 
    SELECT (MSTLISTBRD,MSTMLSNO,MSTACTSTA,MSTSTRNO,MSTSTRNAM,MSTUNITNUM) FROM nht_mls AS mls
        INNER JOIN nht_agtdata AS agt ON mls.MSTLISTBRD = agt.agentid
    ON DUPLICATE KEY UPDATE nht_mlslookup.MSTMLSNO = mls.MSTMLSNO;

Problem is...I think I broke Steve? All he wants to do is this.

ERROR 1241 (21000): Operand should contain 1 column(s)

I'm to understand that Steve doesn't do what I ask because a sub-query isn't returning a result, specifically, the SELECT ...INNER JOIN statement.

When I'm testing him and ask him to preform:

SELECT * FROM jos_mls AS mls
      INNER JOIN jos_activeagents AS active ON mls.MSTLISTBRD = active.AGENTUID

He does what I expect him to do.

But when I ask him to grab specific columns:

SELECT (MSTLISTBRD,MSTMLSNO,MSTACTSTA,MSTSTRNO,MSTSTRNAM,MSTUNITNUM) FROM jos_mls AS mls
    INNER JOIN jos_activeagents AS act ON mls.MSTLISTBRD = act.AGENTUID

He gives me the same error.

Any advice on how to instruct Steve to do his job and quit giving me the run around?

Upvotes: 1

Views: 1164

Answers (1)

Barmar
Barmar

Reputation: 781716

The parentheses around the SELECT list are wrong. That makes it be a single expression, but you can't have commas in an expression in SQL.

INSERT INTO nht_mlslookup (MSTLISTBRD,MSTMLSNO,MSTACTSTA,MSTSTRNO,MSTSTRNAM,MSTUNITNUM) 
SELECT MSTLISTBRD,MSTMLSNO,MSTACTSTA,MSTSTRNO,MSTSTRNAM,MSTUNITNUM FROM nht_mls AS mls
    INNER JOIN nht_agtdata AS agt ON mls.MSTLISTBRD = agt.agentid
ON DUPLICATE KEY UPDATE nht_mlslookup.MSTMLSNO = mls.MSTMLSNO;

Upvotes: 4

Related Questions