SQL UPDATE not working

SQL UPDATE not working

Postby mlawton » Thu Jan 04, 2018 8:36 am

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?
mlawton
 
Posts: 16
Joined: Tue May 10, 2016 1:46 am

Re: SQL UPDATE not working

Postby toms » Thu Jan 04, 2018 4:04 pm

Hi, do you use the nuRunQuery function to execute the statement?
toms
 
Posts: 143
Joined: Wed Nov 08, 2017 8:57 pm

Re: SQL UPDATE not working

Postby mlawton » Thu Jan 04, 2018 11:40 pm

I have tried running it using nuRunQuery and in the phpMyAdmin utility as an SQL script. Same result for both.
mlawton
 
Posts: 16
Joined: Tue May 10, 2016 1:46 am

Re: SQL UPDATE not working

Postby mlawton » Fri Jan 05, 2018 12:20 am

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.
mlawton
 
Posts: 16
Joined: Tue May 10, 2016 1:46 am

Re: SQL UPDATE not working

Postby toms » Fri Jan 05, 2018 3:10 pm

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.
toms
 
Posts: 143
Joined: Wed Nov 08, 2017 8:57 pm

Re: SQL UPDATE not working

Postby mlawton » Sat Jan 06, 2018 5:31 am

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.
mlawton
 
Posts: 16
Joined: Tue May 10, 2016 1:46 am


Return to General