Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

SQL UPDATE not working

Post Reply
mlawton
Posts: 16
Joined: Mon May 09, 2016 6:16 pm

SQL UPDATE not working

Unread post 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?
toms
Posts: 785
Joined: Sun Oct 14, 2018 11:25 am

Re: SQL UPDATE not working

Unread post by toms »

Hi, do you use the nuRunQuery function to execute the statement?
mlawton
Posts: 16
Joined: Mon May 09, 2016 6:16 pm

Re: SQL UPDATE not working

Unread post by mlawton »

I have tried running it using nuRunQuery and in the phpMyAdmin utility as an SQL script. Same result for both.
mlawton
Posts: 16
Joined: Mon May 09, 2016 6:16 pm

Re: SQL UPDATE not working

Unread post 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.
toms
Posts: 785
Joined: Sun Oct 14, 2018 11:25 am

Re: SQL UPDATE not working

Unread post 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.
mlawton
Posts: 16
Joined: Mon May 09, 2016 6:16 pm

Re: SQL UPDATE not working

Unread post 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.
Post Reply