1242 - Subquery returns more than 1 row - mysql

Discussion in 'Databases' started by vahing, Nov 5, 2015.

0
  1. #1
    MySQL gives me an error 1242 - Subquery returns more than 1 row when running

    UPDATE wp_posts
SET wp_posts.post_content = concat((
  SELECT wp_postmeta.meta_value
  FROM wp_postmeta
  WHERE wp_postmeta.post_id = wp_posts.ID && wp_postmeta.meta_key ="mabp_description"),'<center><a href=[php snippet=1] rel="nofollow"><img src="',(
  SELECT wp_postmeta.meta_value
  FROM wp_postmeta
  WHERE wp_postmeta.post_id = wp_posts.ID && wp_postmeta.meta_key ="mabp_thumbnail_url"),'" alt="[php snippet=2]" title="[php snippet=2]"></a></center>');
    Code (markup):
    According to http://stackoverflow.com/questions/12597620/1242-subquery-returns-more-than-1-row-mysql this can be solved with an IN statement, but I don't know how to do that for the above code.

    Your advice is really aprreciated!
     
    Last edited by a moderator: Nov 5, 2015
    vahing, Nov 5, 2015 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,803
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Is all that html meant to be in there?
    maybe post again and wrap [ code] bbcode around it.
     
    sarahk, Nov 5, 2015 IP
  3. neutralhatter

    neutralhatter Greenhorn

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    11
    #3
    You can't solve your problem like this since you cannot rely on the fact that query 1 returns the same amount of records as query 2, and your DBRMS does not know which records to couple. You should perform a query for each record that you are trying to update in a loop => you need a script

    hope this helps,
     
    neutralhatter, Nov 7, 2015 IP
  4. topcoder

    topcoder Well-Known Member

    Messages:
    125
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    138
    #4
    You sub-queries are bring back more then 1 row and based on what you are doing, it's not expecting more then 1 row. You can limit it to 1 row and you won't get the error, but I'm not sure you'll get the results you want. 

    UPDATE wp_posts
SET wp_posts.post_content = concat((
SELECT wp_postmeta.meta_value
FROM wp_postmeta
WHERE wp_postmeta.post_id = wp_posts.ID && wp_postmeta.meta_key ="mabp_description" limit 1),'<center><a href=[php snippet=1] rel="nofollow"><img src="',(
SELECT wp_postmeta.meta_value
FROM wp_postmeta
WHERE wp_postmeta.post_id = wp_posts.ID && wp_postmeta.meta_key ="mabp_thumbnail_url" limit 1),'" alt="[php snippet=2]" title="[php snippet=2]"></a></center>');
    Code (markup):
     
    topcoder, Nov 19, 2015 IP