Unexpected result from update query within SQL Admin / SQL Console (in SQL Project)
The best way to see this problem is to try the following in either Bungee SQL Admin or the SQL Console in a SQL project.
1) Create a table called "stuff" with two columns, "description" and "quantity":
CREATE TABLE stuff (description VARCHAR(20) NOT NULL,quantity INT NOT NULL);
Result: "0 row(s) affected"
2) Now, add data to the columns:
INSERT INTO stuff VALUES ('widget',45),('whatzit',12);
Result: "2 row(s) affected"
3) Let's see what we've created:
SELECT * FROM stuff;
The filled table columns display.
Result: "2 in result set"
Great. Now, why does the command "UPDATE stuff SET quantity=quantity+10;" return the message "0 in result set"?
UPDATE customer SET customer_id=customer_id+10;
Result: "0 in result set"
Indeed the table has been updated, which you can tell by running the "SELECT * FROM stuff" query again. So, why is the return message "0 in result set" instead of "2 row(s) affected", as was the case with the INSERT query? I'm guessing that the "result set" different from the "rows affected". Is that correct?
1) Create a table called "stuff" with two columns, "description" and "quantity":
CREATE TABLE stuff (description VARCHAR(20) NOT NULL,quantity INT NOT NULL);
Result: "0 row(s) affected"
2) Now, add data to the columns:
INSERT INTO stuff VALUES ('widget',45),('whatzit',12);
Result: "2 row(s) affected"
3) Let's see what we've created:
SELECT * FROM stuff;
The filled table columns display.
Result: "2 in result set"
Great. Now, why does the command "UPDATE stuff SET quantity=quantity+10;" return the message "0 in result set"?
UPDATE customer SET customer_id=customer_id+10;
Result: "0 in result set"
Indeed the table has been updated, which you can tell by running the "SELECT * FROM stuff" query again. So, why is the return message "0 in result set" instead of "2 row(s) affected", as was the case with the INSERT query? I'm guessing that the "result set" different from the "rows affected". Is that correct?
1
person has this problem
I have this problem, too!
Tell me when someone solves it.
The more people who report this problem, the more it gets noticed.
The more people who report this problem, the more it gets noticed.
Create a customer community for your own organization
Plans starting at $19/month
-
Inappropriate?You're correct that the message should be "X row(s) affected." The "0 in result set" is correct because there are no results returned from an update but it's not a useful message since it will always be 0.
I entered feature request #7936.
Loading Profile...


EMPLOYEE