Link to home
Start Free TrialLog in
Avatar of joomla
joomlaFlag for Australia

asked on

SQL Statement not working

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.

Avatar of Bill Bach
Bill Bach
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I agree with above on the reason. You will need to use a MySQL prepared statement to run the dynamic sql created - http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

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


or
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.
Avatar of joomla

ASKER

Thanks, helps me a great deal.
regards
M