July 24, 2008 at 1:54 pm
A workaround for those curious, simply create a view of your statement as suggested by MSDN, like so:
CREATE VIEW GetAroundSQL2005AnnoyingReadOnlyFeature
AS
SELECT Player.FirstName, Player.LastName, Team.Name
FROM Player INNER JOIN
PlayerTeam ON Player.Id = PlayerTeam.PlayerId INNER JOIN
Team ON PlayerTeam.TeamId = Team.Id
WHERE Player.LastName = 'Graham' AND Team.Name LIKE 'X%'
Then open your view and edit away! The next time you need to do the same, use ALTER VIEW. There are several limits on the kind of views that are still editable.
MSDN Article titled 'Modifying Data Through a View':
http://msdn.microsoft.com/en-us/library/ms180800.aspx
Bug report for this feature:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=148844&wa=wsignin1.0
Another workaround using a subquery:
https://connect.microsoft.com/SQLServer/feedback/Workaround.aspx?FeedbackID=148844
July 24, 2008 at 2:42 pm
I just did this as a test:
create table UpdT1 (
ID int identity primary key,
Col1 varchar(100))
go
create table UpdT2 (
ID int identity primary key,
Col1 varchar(100))
go
insert into dbo.updt1 (col1)
select 'x'
from dbo.numbers
insert into dbo.updt2 (col1)
select 'y'
from dbo.numbers
go
create view UpdTest
as
select t1.id as t1id, t2.id as t2id, t1.col1 as T1Col1, t2.col1 as T2Col1
from dbo.updt1 t1
inner join dbo.updt2 t2
on t1.id = t2.id
When I right-clicked the view and opened it, then directly edited the data, it worked just fine. Edited with no problem.
I'm not consciously using any sort of "work-around". That's just a standard view.
Try that test, see if it works the way you want it to. If not, check to see whether you have a missing update on Management Studio (I'm using 9.00.3042.00). If so, then it's something about the view you're writing or the column you're updating.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 2:43 pm
Danster (4/30/2007)
As one of my first forays, I thought I'd whip up a typical view. No
problems, but when I execute, the results pane say 'cell is read only'.
I'm used to directly editing within the results pane in Enterprise Manager
(yeah, I know...I'm careful) but in SSMS the view doesn't allow me to edit
data.
Now I'm confused. How was the work around you posted different that what you were trying to do in the first place?
July 24, 2008 at 3:08 pm
Danster (7/24/2008)
There's nothing "at play".I just like editing data directly in a multi-table view. It is fast, easy, safe (if you're careful) and convenient.
Usually it is to quickly make changes to source data which then will be viewable in the app which uses that database.
It was oh so easy in 2000 Enterprise Manager & 2005 views have just screwed up that ease.
How about actually posting the DDL for a view you cannot edit, along with the DDL for the underlying tables used by the view?
I just tried it myself, and had no trouble editing either the SERVER_NAME or DATABASE_NAME columns from view V_SERVER_DATABASE created with the following code.
IF OBJECT_ID('[dbo].[V_SERVER_DATABASE]','V') IS NOT NULL
DROP VIEW [dbo].[V_SERVER_DATABASE]
GO
IF OBJECT_ID('[dbo].[T_DATABASE]','U') IS NOT NULL
DROP TABLE [dbo].[T_DATABASE]
GO
IF OBJECT_ID('[dbo].[T_SERVER]','U') IS NOT NULL
DROP TABLE [dbo].[T_SERVER]
GO
CREATE TABLE [dbo].[T_SERVER](
[SERVER_ID] [int] IDENTITY(1,1) NOT NULL,
[SERVER_NAME] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED
([SERVER_ID] ASC))
go
CREATE TABLE [dbo].[T_DATABASE](
[DATABASE_ID] [int] IDENTITY(1,1) NOT NULL,
[DATABASE_NAME] [sysname] NOT NULL,
[SERVER_ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
([DATABASE_ID] ASC))
GO
ALTER TABLE [dbo].[T_DATABASE] WITH CHECK
ADD CONSTRAINT [FK_T_DATABASE_T_SERVER]
FOREIGN KEY([SERVER_ID])
REFERENCES [dbo].[T_SERVER] ([SERVER_ID])
GO
create view [dbo].[V_SERVER_DATABASE]
as
select
a.SERVER_ID,
a.SERVER_NAME,
b.DATABASE_ID,
b.DATABASE_NAME
from
[dbo].[T_SERVER] a
join
[dbo].[T_DATABASE] b
on a.SERVER_ID = b.SERVER_ID
GO
insert into T_SERVER (SERVER_NAME)
select 'SERVER_1' union all
select 'SERVER_2' union all
select 'SERVER_3'
order by 1
GO
insert into T_DATABASE (DATABASE_NAME, SERVER_ID)
select 'DATABASE_1' ,1 union all
select 'DATABASE_2' , 2 union all
select 'DATABASE_3' , 3
order by 1
GO
SELECT * FROM [dbo].[V_SERVER_DATABASE]
January 14, 2010 at 10:49 pm
Looks like the same limitation is still in SQL 2008.
My workaround for the cell read-only problem with multi-table queries was to keep using SQL 2000 for all development work. I recently installed Win 7, so decided to try SQL 2008 but it looks like the same limitation is still there. Sadly, only there seems to be some issues installing SQL 2000 on Win 7.
I guess I can set up an XP box to run SQL 2000, but it would be nice at some point if SQL 2005/2008 or some future year would be able to match the functionality of SQL 2000.
Looks like this thread has been going a few years now. Has anybody found a better solution than staying with SQL 2000?
January 15, 2010 at 4:03 pm
Dave,
Yes limitation is still there in 2008. And yes I have been frustrated all this time over the past few years. I am still finding that something that used to take me a few seconds now takes me about 2 or 3 minutes.
I don't know if anyone from Microsoft is listening...
We just want to edit a cell from a multi-table view. Is that so hard?
I keep a Virtual PC with XP Pro and SQL Server 2000 but that doesn't help nowadays when the database I need to edit actually lives on a 2005/2008 SQL Server.
January 15, 2010 at 6:54 pm
I'm still having a hard time understanding how this thread could possibly still be active...
The easiest solution is to type fast, such as:
UPDATE AFFECTED_TABLE
SET FIELD_NAME = value
WHERE PRIMARY_KEY_FIELD = primary_key_value
Honestly, it just doesn't take 2 to 3 minutes to do that, even including the time to copy and paste the primary key value for that table. If it reoccurs a lot for a given table, set up a stored procedure that updates that specific table using the primary key value, the field name, and the new value as parameters.
Getting used to doing such things is a GOOD IDEA, because it encourages more appropriate behaviors related to data integrity. Are we done now?
Steve
(aka sgmunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 25, 2010 at 8:04 am
I agree there is an intermittent need for this and have just updated to SQL 2005 and hit this gotcha.
My query was of the form
select stuff from table1 t1
inner join table2 t2 on t1.ref = t2.ref
where this and that and theOther
in order to allow editing I had to switch from a join so...
select stuff from table1 t1
where this and that and theOther
and ref in (select ref from table2 where doodly and clink)
hope that helps someone
February 25, 2010 at 8:15 am
The important thing to remember is that being able to update a record requires that there be a unique record or set of records to update, such that there's no concievable ambiguity on which record to update. A recordset that delivers one record from one table, but multiple records from a joined table, doesn't provide that level of uniqueness if you're trying to update the table that's contributing multiple records. Thus when you try to update a view, you need to know if the view will be returning a sufficiently unique recordset. This should force folks to give their database design more thought in terms of exactly how you do EVERYTHING you might want or need to do to it, and to go through that kind of exercise for EVERY time you need something new that goes into that database.
Steve
(aka sgmunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 25, 2010 at 8:18 am
As the idiot who was rushing and didn't see the extra pages of replies I offer my apologies 🙂
In my instance there are c2000 records with forenames but no Title and Gender set... yes we've addressed the import issues but that doesn't clean the data. I reckon this way is quicker than building a neural net to recognise the gender of a name 😀
February 25, 2010 at 8:28 am
Yes, and besides, there's just no way even a neural net can handle names like Terry, Chris, Pat (in this case, maybe even an adrogenous individual (SNL reference, LOL)), Jamie, Sam (often short for Samantha), Billy (could be Billy Jean), and numerous others....
Steve
(aka sgmunson)
;-);-);-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply