SQL error in concat with two subqueries

Question

I am trying this code. However i am getting a syntax error. What is the correct way?

UPDATE Classificacao AS C
SET C.ClassificacaoCPV_id = (
    SELECT CONCAT(
      SELECT ClassificacaoCPV FROM Classificacao, 
      SELECT substring_index(`ClassificacaoCPV_id`,'|',-1) FROM Classificacao)
 )

ERROR: SQL Error (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 'SELECT C.ClassificacaoCPV FROM Classificacao,
SELECT substring_index(`Class' at line 4

You have no filters on the subquery. Is that intentional? You are likely getting more than one result back when MySQL wants just one result. — mechanical_meat
– mechanical_meat, Commented Apr 2, 2013 at 16:09
@KugathasanAbimaran, you are right, but the problem remains. — daniel__
– daniel__, Commented Apr 2, 2013 at 16:14

Ryan Goltry · Accepted Answer · 2013-04-02 16:26:58Z

What I can pull from this is that you want to create an ClassificatiocaoCPV_id that is the concatenation of ClassificacaoCPV and a portion of the existing ClassificatiocaoCPV_id.

UPDATE Classificacao
   SET ClassificacaoCPV_id = 
          CONCAT(ClassificacaoCPV, substring_index(ClassificacaoCPV_id,'|',-1))

This is a little dangerous to do on the fly. Your assumptions will only work once and then you may be out of luck on pulling the ClassificacaoCPV_id column again. A couple of recommendations for you: 1) Run the following query and make sure the results are what you expect before updating the column that you are also pulling data from. 2) Add an additional column such as ClassificacaoCPV_id_new for the new ID column instead of updating the column that you are pulling part of the data from.

select CONCAT(C.ClassificacaoCPV, substring_index(ClassificacaoCPV_id, '|',-1) from ClassificacaoCPV

Remember to measure twice, because you only get to cut once.

Gordon Linoff · Accepted Answer · 2013-04-02 16:10:02Z

0

You need additional parentheses for subqueries:

UPDATE Classificacao AS C
SET C.ClassificacaoCPV_id = (
    SELECT CONCAT(
      (SELECT ClassificacaoCPV FROM Classificaca o), 
      (SELECT substring_index(`ClassificacaoCPV_id`,'|',-1) )
    FROM Classificacao)
 )

This assumes that each subquery returns one value. You might need to use group_concat() for this query.

answered Apr 2, 2013 at 16:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

daniel__ Over a year ago

Same error. I also tried the subqueries, and they work well separately.

Collectives™ on Stack Overflow

SQL error in concat with two subqueries

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related