Update mysql field using CONCAT and SELECT

Question

EDIT

After removing my silly mistake of INTO (I was working with INSERTS and just keep going) the error below is showing. Still not working:

Affected rows: 0

[Err] 1093 - You can't specify target table 'tbl' for update in FROM clause

I'm trying to create an update where I select all the previous data in the column, add a complementary string and save it as new data. The code is below (with the error)

Using only the select, the result:

set @id = 3;

SELECT tbl_alias.string_id 
FROM tbl as tbl_alias
WHERE id = @id

-- the output `3,10,8,9,4,1,7,11,5,2,6,12`

I also tried with this query (the output is what I want)

SELECT CONCAT((
    SELECT tbl_alias.string_id 
    FROM tbl as tbl_alias
    WHERE id = @id
),',13,14,15,16') AS X

-- the output `3,10,8,9,4,1,7,11,5,2,6,12,13,14,15,16`

But after replacing the select below. It brings the same error.

The query

set @id = 3;

UPDATE INTO tbl 
SET string_id = 
    CONCAT((
        SELECT tbl_alias.string_id 
        FROM tbl as tbl_alias
        WHERE id = @id
    ),',13,14,15,16') WHERE id = @id;

The error

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' INTO tbl SET string_id = CONCAT(( SELECT tbl_alias.string_id ' at line 1

It's probably the CONCAT together with SELECT. But I didn't find the solution...

You are using INTO on an UPDATE (used on an INSERT but not for an UPDATE). — Kickstart
– Kickstart, Commented Mar 13, 2014 at 12:14
I was working with inserts, I just keep going. The error persists after removing INTO — Michel Ayres
– Michel Ayres, Commented Mar 13, 2014 at 12:17

Michel Ayres · Accepted Answer · 2014-03-13 12:24:26Z

25

Do you need the sub query?

UPDATE tbl 
SET string_id = CONCAT(string_id, ',13,14,15,16')
WHERE id = @id;

Note that in MySQL you cannot modify using an UPDATE the table that is used in the sub query (although there are fiddles around it):-

https://dev.mysql.com/doc/refman/5.5/en/subqueries.html

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.

edited Mar 13, 2014 at 12:24

Michel Ayres

6,02511 gold badges67 silver badges97 bronze badges

answered Mar 13, 2014 at 12:20

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Michel Ayres Over a year ago

I need the data inside the column. string_id == ??

Kickstart Over a year ago

You original update appears to be adding ',13,14,15,16' onto the end of the field string_id. That is what this query does.

Michel Ayres Over a year ago

The second query in my question answer your question

Michel Ayres Over a year ago

I want to get the data in the column, add extra info and save it again. "-- the output 3,10,8,9,4,1,7,11,5,2,6,12,13,14,15,16"

Kickstart Over a year ago

If you are just doing the update then I think the sub query is not required, as you can just concat ',13,14,15,16' onto the end of string_id. If string_id had a value of 3,10,8,9,4,1,7,11,5,2,6,12 then this update would result in it now having a value of 3,10,8,9,4,1,7,11,5,2,6,12,13,14,15,16

|

Michel Ayres · Accepted Answer · 2014-03-13 12:21:06Z

2

Try to use UPDATE without INTO:

set @id = 3;

UPDATE tbl 
SET string_id = 
    CONCAT((
        SELECT tbl_alias.string_id 
        FROM tbl as tbl_alias
        WHERE id = @id
    ),',13,14,15,16') WHERE id = @id;

Update:

Try this:

set @id = 3;

UPDATE tbl 
SET string_id = 
    CONCAT(SELECT string_id FROM (
        SELECT tbl_alias.string_id 
        FROM tbl as tbl_alias
        WHERE id = @id
    ) t1 ,',13,14,15,16') WHERE id = @id;

edited Mar 13, 2014 at 12:21

Michel Ayres

6,02511 gold badges67 silver badges97 bronze badges

answered Mar 13, 2014 at 12:13

Nailgun

4,1894 gold badges35 silver badges47 bronze badges

2 Comments

Michel Ayres Over a year ago

Affected rows: 0 and [Err] 1093 - You can't specify target table 'tbl' for update in FROM clause. btw, was a silly mistake of mine, thanks for pointing that out. Still not working =/

Michel Ayres Over a year ago

[Err] 1583 - Incorrect parameters in the call to native function 'CONCAT'

Oleksii Kyslytsyn · Accepted Answer · 2016-11-01 15:10:12Z

0

This case helps sometimes to update ALL column data for TESTING purpose:

UPDATE customer_profile 
  SET businessId = CONCAT(businessId, ' new customer ', (id + 1))

answered Nov 1, 2016 at 15:10

Oleksii Kyslytsyn

2,3603 gold badges40 silver badges61 bronze badges

Collectives™ on Stack Overflow

Update mysql field using CONCAT and SELECT

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related