Page 1 of 1

SQL UPDATE not working

Posted: Thu Jan 04, 2018 12:06 am
by mlawton
I am having problems with the following SQL statement:

UPDATE vendor SET period_name_fq1 = '2017H2';

The statement executes, returning 0 rows updated. I confirmed that no rows have been changed. Note that none of the rows have this value currently. My expected outcome is for all rows in this table to be updated. I tried adding a WHERE clause but it did not make a difference - still 0 rows updated. I am using MySQL 5.7. "period_name_fq1" is a VARCHAR(7) column in the table "vendor".

I must be missing something basic, but I cannot see what. Has anyone run into this problem?

Re: SQL UPDATE not working

Posted: Thu Jan 04, 2018 7:34 am
by toms
Hi, do you use the nuRunQuery function to execute the statement?

Re: SQL UPDATE not working

Posted: Thu Jan 04, 2018 3:10 pm
by mlawton
I have tried running it using nuRunQuery and in the phpMyAdmin utility as an SQL script. Same result for both.

Re: SQL UPDATE not working

Posted: Thu Jan 04, 2018 3:50 pm
by mlawton
Note: I made two copies of the vendor table, one using CREATE TABLE temp LIKE vendor (I then inserted all the records from vendor into temp), and the other using CREATE TABLE temp2 AS SELECT * FROM vendor. The first replicates all the indices while the second does not. For both copies, when I tried using the UPDATE statement it worked, updating all rows. Yet when I try the UPDATE statement on the original vendor table it does not update any rows. I don't know why.

Re: SQL UPDATE not working

Posted: Fri Jan 05, 2018 6:40 am
by toms
Since you get the same result in phpMyAdmin, the issue is not related to nuBuilder. You might want to ask your question in a mysql forum. V3 is no longer supported anyway and nuSoftware staff seems to have stopped answering questions in this v3-forum.

Re: SQL UPDATE not working

Posted: Fri Jan 05, 2018 9:01 pm
by mlawton
You are correct, this appears to be a MySQL issue. I wondered if anyone has run into this before using nuBuilder. In any case, I have used a workaround which is to create a table like vendor, populate it with all records from vendor, then rename vendor to vendor_old and rename the new table to vendor. I can now update the new vendor table properly. The old vendor table must be defective/corrupted somehow, although I get no error messages to that effect.