Getting error like Operand should contain 1 column(s), When duplicating a record with some column values changes only required

Question

Here is the code that I have tried to make a duplicate of the record with help of id, by keeping the original record with one column value change. I have tried without the id also, as it gets auto increment when inserting, but got errors like You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near.

INSERT INTO 
    `org_emps`
    (
        3,
        `admin_id`, 
        `emp_branch_id`, 
        `emp_name`, 
        'ITSe08A 0002', 
        `emp_designation`, 
        `emp_department`, 
        `emp_father_husband`, 
        `emp_mother`, 
        `emp_wife`, 
        `emp_nominee`, 
        '02-03-2018', 
        `emp_dob`, 
        `emp_gender`, 
        `emp_m_status`, 
        `emp_father_husband_dob`, 
        `emp_mother_dob`, 
        `emp_wife_dob`, 
        `emp_nominee_dob`, 
        `emp_aadhar`, 
        `emp_PAN`, 
        `emp_bank_ac_num`, 
        `emp_bank_ifsc`, 
        `emp_bnk_branch_address`, 
        `emp_pf_status`, 
        `emp_esi_status`, 
        `emp_income_tax`, 
        `emp_tds`, 
        `emp_photo`, 
        `emp_addr1`, 
        `emp_addr2`, 
        `emp_phone`, 
        `emp_email`, 
        `emp_w_off`, 
        `emp_ot_rate`, 
        'March 2, 2018, 8:46 am', 
        1, 
        `emp_key`
    ) 
    SELECT (
        `id`, 
        `admin_id`, 
        `emp_branch_id`, 
        `emp_name`, 
        `emp_org_id`, 
        `emp_designation`, 
        `emp_department`, 
        `emp_father_husband`, 
        `emp_mother`, 
        `emp_wife`, 
        `emp_nominee`, 
        `emp_doj`, 
        `emp_dob`, 
        `emp_gender`, 
        `emp_m_status`, 
        `emp_father_husband_dob`, 
        `emp_mother_dob`, 
        `emp_wife_dob`, 
        `emp_nominee_dob`, 
        `emp_aadhar`, 
        `emp_PAN`, 
        `emp_bank_ac_num`, 
        `emp_bank_ifsc`, 
        `emp_bnk_branch_address`, 
        `emp_pf_status`, 
        `emp_esi_status`, 
        `emp_income_tax`, 
        `emp_tds`, 
        `emp_photo`, 
        `emp_addr1`, 
        `emp_addr2`, 
        `emp_phone`, 
        `emp_email`, 
        `emp_w_off`, 
        `emp_ot_rate`, 
        `emp_created_on`,
        `emp_type`, 
        `emp_key`
    ) 
    FROM 
        `org_emps` 
    WHERE 
        id = 2

You seem to be mixing all kinds of stuff a bit randomly. Insert, Select, column names, values etc. The error might say "an error" but there are definitely more.. — M. Eriksson, Mar 02 '18 at 08:00
It probably is your date (`'March 2, 2018, 8:46 am'`). Don't think mysql recognizes it as a date format — Michel, Mar 02 '18 at 08:01
@Magnus Eriksson there was not a select in the original question. — P.Salmon, Mar 02 '18 at 08:01
@P.Salmon - Yes there was. In the middle of the one line string. I didn't just randomly add SQL key words... I just added line breaks and tabs. — M. Eriksson, Mar 02 '18 at 08:03
Thanks for the replies. There's no issues with date, it is a TEXT type column. — Dara Naveen Kumar, Mar 02 '18 at 08:11
_"There's no issues with date, it is a TEXT type column"_ - You're still mixing column names and values. Example: in the insert, the first value is `3` and the second value seems to be a column name. — M. Eriksson, Mar 02 '18 at 08:38
Okay, Then. How can I add id column value? for duplicating record. Let me know pls. — Dara Naveen Kumar, Mar 02 '18 at 09:27
Or else, please suggest me an example that how can i make a duplicate record of a row in my table by making them with two or three columns change. — Dara Naveen Kumar, Mar 02 '18 at 09:29

score 0 · Answer 1 · answered Mar 02 '18 at 08:01

0

Number of insert columns and their values should be same.Your insert statement column and values columns number are not same here.

answered Mar 02 '18 at 08:01

Ms.KV

214
2
10

That's only one of several issues with that query. – M. Eriksson Mar 02 '18 at 08:04
What my intention is, I have a employee record of type 0, i just want to make a duplicate and make the original record type column as 3, the new record column type is 1. That's it. If you need more explanation, I will try to give you more. – Dara Naveen Kumar Mar 02 '18 at 08:18
column id is auto incremented, is there any need to mention that column also while making duplicate? – Dara Naveen Kumar Mar 02 '18 at 08:19
Please, help me to achieve this.. I'm on urgent work requirement. – Dara Naveen Kumar Mar 02 '18 at 08:19
And, i have tried id column as it is in both insert and select clauses. but no use of it. it remains same error. – Dara Naveen Kumar Mar 02 '18 at 08:21
https://stackoverflow.com/questions/9156340/how-to-copy-a-row-and-insert-in-same-table-with-a-autoincrement-field-in-mysql By this I'm trying. – Dara Naveen Kumar Mar 02 '18 at 08:41

Jens · Answer 2 · 2018-03-02T08:43:05.913

0

Constants values must be part of the select part:

INSERT INTO 
    `org_emps`
    (
        `id`,
       `admin_id`, 
       `emp_branch_id`, 
       `emp_name`, 

...   ) 
    SELECT (
        3, 
      `admin_id`, 
      `emp_branch_id`, 
      `emp_name`, 

   ...
    ) 
    FROM 
        `org_emps` 
    WHERE 
        id = 2

edited Mar 02 '18 at 08:43

answered Mar 02 '18 at 08:02

Jens

67,715
15
98
113

So, What do you say, I did't get. Please help me to fulfill my requirement. – Dara Naveen Kumar Mar 02 '18 at 08:25
or else, let me know. How can I duplicate a record in my MySQL db, having id (primary auto increment key of my table) by 1. Keep the original record as it is, with just changing the value of column emp_type. 2. Make a duplicate with auto incremented id, and along with some value changes in couple of columns like emp_doj, emp_type, etc.. – Dara Naveen Kumar Mar 02 '18 at 08:27
@DaraNaveenKumar read the code snippet and you should see what i mean. – Jens Mar 02 '18 at 08:32
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '3,`admin_id`, `emp_branch_id`, `emp_name`, 'ITSe08A 0002', `emp_designation`, `e' at line 1 – Dara Naveen Kumar Mar 02 '18 at 08:34
Oh!! Okay my dear. Thank you. – Dara Naveen Kumar Mar 02 '18 at 08:34

Getting error like Operand should contain 1 column(s), When duplicating a record with some column values changes only required

2 Answers2