SQL Statement not working

Posted on 2011-10-07
Last Modified: 2012-08-14
SELECT itemid, CONCAT(  "insert into tblmembers (ID,", GROUP_CONCAT( b.fieldname ) ,  ") values (", itemid,  ",", GROUP_CONCAT(  "'", data_txt,  "'" ) ,  ");" )
FROM jos_sobi2_fields_data AS a
JOIN tablefields AS b ON (
a.`fieldid` = b.`fieldid`
GROUP BY itemid
ORDER BY itemid
LIMIT 0 , 30

Can anyone tell me why this statement doesn't create records in the table 'tblmembers'
tblmembers & jos_sobi2_fields_data & tablesfields all exist and are structured properly.

Question by:joomla
    LVL 28

    Expert Comment

    by:Bill Bach
    The second field is attempting to create a SQL INSERT statement, but the INSERT statement is never run. The out of this is the series of INSERTs, which you should then copy into a script file -- then run the script.

    If you really want the INSERTs to fire, then this should look more like INSERT INTO table SELECT ... syntax.
    LVL 51

    Accepted Solution

    It looks like you are trying to display a built up insert query / command - like dynamic SQL.

    While it will not insert, what does it produce ?

    Chances are you need to copy the result of that query and then run that.

    It probably produces an output like :

    [itemid]   [unknown]
    101         insert into tblmembers (id, field1, field2, field3) values (101, 'data 1', 'data 2', 'data 3')
    102         insert into tblmembers (id, field1, field2, field5) values (102, 'data 1', 'data 2', 'data 5')

    Open in new window

    Which is the good bit about a group_concat() function - for items that have columns listed vertically, it can kind of "pivot" the columns into a comma delimited list - ideal for creating dynamic sql. Have a look at :

    But If the column names for tblmembers are already known, then you can achieve the same results by first doing an insert, then update the columns accordingly.

    INSERT tblmembers (id)
    SELECT distinct a.itemid
    FROM jos_sobi2_fields_data AS a 
    JOIN tablefields AS b ON a.fieldid = b.fieldid

    Open in new window

    then simply update the columns...

    update tblmembers set field1 = data1
    FROM jos_sobi2_fields_data AS a 
    JOIN tablefields AS b ON a.fieldid = b.fieldid
    WHERE tblmembers.ID = a.itemid
    AND b.fieldname = 'field1'

    Open in new window

    of course you would need to repeat for every field.

    If however you are using a programming language, then you can use the group_concat() to return the string needed to do the insert cleanly...

    LVL 59

    Expert Comment

    by:Kevin Cross
    I agree with above on the reason. You will need to use a MySQL prepared statement to run the dynamic sql created -

    What exactly are you wanting to do? Just note that UPDATE WITH JOIN in MySQL is in the UPDATE, i.e.:
    UPDATE a, b
    SET a.column = b.column
    WHERE a.key = b.key

    Open in new window

    UPDATE a JOIN b ON a.key = b.key
    SET a.column = b.column

    Open in new window

    Otherwise, the syntax shown by Mark should work.

    Author Closing Comment

    Thanks, helps me a great deal.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
    The purpose of this article is to demonstrate how we can use conditional statements using Python.
    This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
    The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now