[This topic is migrated from our old forums. The original author name has been removed]
I know you can edit directly in a Result Set under certain conditions but it would be great if there was a way you could provide hints to a sql query such that you could update/delete in a Result Set even when the query was across tables.... something like:
select u.FirstName, u.LastName, p.PKPermission_id, p.PermissionName
from User u, Permission p @KeyTable
where u.PKUser_id = p.FKUser_id;
In the above I'm simply identifying the "key" table such that if I try to delete a row from the result set it knows the deletion should happen on the Permission table, not the User table (this also assumes DbVis is able to figure out that PKPermission_id is the primary key on the table and hence knows how to delete).
It should also allow updates to PermissionName.
Although now that I'm thinking about it, it would also be nice if it would allow updates to FirstName and LastName (although maybe the result set would require the inclusion of the PK for the User table to support).
The basis for this request is simply due to 3rd normal form databases.. I've got tables with lots of IDs but no textual names... so to provide a list of understandable results I have to join tables to get that-- but the end result is I'm often looking for a specific row in a single table to delete or update.
Jason,
Sorry for the delay.
I've just open a ticket for your request.
Regards
Roger
a
anonymous
said
over 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Delete/Update in Result Set
Any update on this one? Seems like once a day I run into this recently :)
Roger Bjärevall
said
over 10 years ago
[This reply is migrated from our old forums.]
Re: Delete/Update in Result Set
Jason,
There has been no updates on this.
Regards
Roger
a
anonymous
said
over 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Delete/Update in Result Set
Anything?
Roger Bjärevall
said
over 10 years ago
[This reply is migrated from our old forums.]
Re: Delete/Update in Result Set
Hi Jason,
This is still not planned as the demand is too low.
Regards
Roger
a
anonymous
said
over 10 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Delete/Update in Result Set
Do you have sufficient privileges to create database views? Most of the database products I'm familiar with allow updates to the base table of a view. So if you create this view:
CREATE VIEW my_user_view AS
select u.PKUser_id, u.FirstName, u.LastName, p.PKPermission_id, p.PermissionName
from User u
left outer join Permission p on u.PKUser_id = p.FKUser_id;
... you should be able to insert, update or delete records in the User table via the view (subject to any constraints on the tables). Note that the primary key for the base table must be one of the view columns.
You didn't mention which DBMS you use but based on the join syntax in your example I'm guesing it might be Oracle. If so, there are good examples in the Oracle SQL Reference under "CREATE VIEW" including sample code to determine which columns in a given view are updateable.
anonymous