0

Created query.

With this query update table 2_1_transactionpartners.

Then where value in another table (2_1_journal) is the same as value in table 2_1_transactionpartners, update the another table.

BEGIN; 
UPDATE 2_1_transactionpartners SET 
PreviousCompanyName = CASE NumberRenamed WHEN ? THEN CompanyName END,
CompanyName = CASE NumberRenamed WHEN ? THEN ? END,
/* others WHEN ? THEN ? END */
BankAccountNumber = CASE NumberRenamed WHEN ? THEN ? END 
WHERE NumberRenamed IN (?); 

/* here starts problem */
UPDATE 2_1_journal 
INNER JOIN 2_1_transactionpartners ON 
(2_1_journal.TransactionPartnerName = 2_1_transactionpartners.PreviousCompanyName) 
SET 2_1_journal.TransactionPartnerName = 2_1_transactionpartners.CompanyName; 

COMMIT;

When tested see that all updates. But get such error SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

If divide the query into two queries. Second query like 

UPDATE 2_1_journal 
INNER JOIN 2_1_transactionpartners ON (2_1_journal.TransactionPartnerName = 2_1_transactionpartners.PreviousCompanyName) 
SET 2_1_journal.TransactionPartnerName = 2_1_transactionpartners.CompanyName;

Then all works without error. Can I get these two updates to work in one query without errors?

Update

After execution of the statement (with 2 queries) placed $stmt->closeCursor(); and no error

user2465936
  • 1,030
  • 4
  • 17
  • 32
  • possible duplicate of [PDO support for multiple queries (PDO\_MYSQL, PDO\_MYSQLND)](http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) – Barmar Oct 10 '13 at 19:27
  • Not good. So what solution? Two queries and no other option? – user2465936 Oct 10 '13 at 19:27
  • Look at the duplicate question I linked to. – Barmar Oct 10 '13 at 19:27
  • @Barmar In link question-answer seems related to Zend framework. After some experiments found that problem in my code was not because 2 queries in one statement, but because of multiple statements. First statement `update`, next statement `select` (in my question I did not mention about `select` statement). I removed `select` statement and the `update` statement with 2 queries works and without errors. Could you write some comments? My conclusion is that PDP supports multiple queries in one statement. – user2465936 Oct 11 '13 at 07:10
  • I can't comment in more detail, I'm not really that familiar with the error. But there are other questions in the **Related** list that are similar, have you reviewed them? – Barmar Oct 11 '13 at 16:20
  • Yes, I read (not all at the moment)... OK, in any case all works as expected. Latter would understand better. Now see from experiments: if after statement with multiple queries no other statement, then all works. If there is other statement, must write $stmt->closeCursor(); to "close" statement with multiple queries – user2465936 Oct 11 '13 at 17:22

0 Answers0