How can I translate SQL code into something that Joomla will take?

Question

I am in the same case discussed on https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table, that is, I need to copy the value of a column from tableA to tableB based on a common column in each table.

I have used the suggested code inside of PHPMyAdmin, and it works perfectly:

UPDATE TableB 
SET TableB.value = (
    SELECT TableA.value 
    FROM TableA
    WHERE TableA.name = TableB.name
);

My problem is that I have been unable to translate this into something that I can use inside of my mod_xxx.php file. On the same Stack Overflow page, there is a suggested code that uses mysql_query and supposedly works, but Joomla doesn´t accept it. I know that any code has to be translated to Joomla's coding standards but I have spent three complete days trying it with no success.

I have tried the following code unsuccessfully:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query ->UPDATE ('#__gruposlocales')
       ->SET ('#__gruposlocales.name') =
             (SELECT ('#__community_groups.name')
              FROM ('#__community_groups')
              WHERE ('#__community_groups.ownerid = #__gruposlocales.id'));
$db->setQuery($query);
$result = $db->execute(); 

As you may guess, I am not knowledgeable on PHP coding, so I am in serious need of your help, please. My question is how can I translate the upper code into something that Joomla will take?

Answer 1

You can do the following:

$db = JFactory::getDbo();
$sql = "UPDATE TableB SET TableB.value = (SELECT TableA.value FROM TableA WHERE TableA.name = TableB.name);
$db->setQuery($sql);
$db->execute();

Answer 2

Joomla should be able to understand this... You must let Joomla's query building functions render the "child query"/subquery before placing it inside of the "parent query"/main query.

Here are some other answers of mine that may prove helpful while writing/testing your own query:

• Method for creating a subquery using JDatabase
• Correct ways to verify update query is successful under conditions

Code: (untested)

$subquery = $db->getQuery(true)
    ->select("cg.name")
    ->from("#__community_groups AS cg")
    ->where("cg.ownerid = gl.id");

$query = $db->getQuery(true)
    ->update("#__gruposlocales AS gl")
    ->set("gl.name = ({$subquery})");

$db->setQuery($query);
$db->execute();

I am deliberately omitting all quoting techniques in my answer because they are unnecessary fir stability/security in this snippet and would only serve to make the snippet more difficult to read.

As for discussions on how/when/why to quote query entities, how to view the rendered query with dump(), how to write a try{}catch{} block to aid in debugging, and how to check for affected rows -- please see my other mysql-tagged answers in this community.