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?
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
SQL UPDATE not working
-
- Posts: 785
- Joined: Sun Oct 14, 2018 11:25 am
-
- Posts: 16
- Joined: Mon May 09, 2016 6:16 pm
Re: SQL UPDATE not working
I have tried running it using nuRunQuery and in the phpMyAdmin utility as an SQL script. Same result for both.
-
- Posts: 16
- Joined: Mon May 09, 2016 6:16 pm
Re: SQL UPDATE not working
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.
-
- Posts: 785
- Joined: Sun Oct 14, 2018 11:25 am
Re: SQL UPDATE not working
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.
-
- Posts: 16
- Joined: Mon May 09, 2016 6:16 pm
Re: SQL UPDATE not working
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.