|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Update Query question.I do have a question about a query on update. I appreciate if anyone could help me. Here is the table I have: Emp ID Lname Fname OfficeID 1 A Bobo 1 2 B Coco 2 3 C Jojo 1 Office ID Name StateID 1 HQ 1 2 WareHouse 2 State ID Name 1 WA 2 CA I want to move the HQ from WA to NY. First I do this: insert into state (Name) values ('NY'); Then, I do this: Update office a set a.StateID = (Select b.ID from State b where b.Name = 'NY') where a.Name = 'HQ'; The database complains: "Operation must use an updateable query." How could I fix that? Thanks. Try it without table aliases:
Update office set office.StateID = (Select State.ID from State where State.Name = 'NY') where office.Name = 'HQ'; ML --- http://milambda.blogspot.com/ Thanks
I have actually tried to removed the table alias and it didn't work. Would it possibly be the problem of I am doing it in Access? If I run it in SQL 2000, will this work? Show quote "ML" <M*@discussions.microsoft.com> ¦b¶l¥ó news:C3FA906B-87A4-419A-A5D8-04E1594FE6AC@microsoft.com ¤¤¼¶¼g... > Try it without table aliases: > > Update office > set office.StateID = (Select State.ID from State where State.Name = 'NY') > where office.Name = 'HQ'; > > > ML > > --- > http://milambda.blogspot.com/ Trinidad (t@t.com) writes:
> I have actually tried to removed the table alias and it didn't work. Possibly. There are considerable differences between SQL Server and> Would it possibly be the problem of I am doing it in Access? If I run > it in SQL 2000, will this work? Access. In this newsgroup you will get answers that work on SQL Server, and may not run on Access. For assistance on Access, you should ask in a newsgroup for Access. Also, never say "it didn't work". Specify in what way it didn't work, that is did you get an error message, unexpected results or what. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Please explain what "it didn't work" means. Were there errors? No results?
Unexpected results? What? ML --- http://milambda.blogspot.com/ > The database complains: "Operation must use an updateable query." When I run your query from QA or SSMS, I get:Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'a'. Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'where'. The syntax check succeeds after removing the alias from the table: Update office set office.StateID = (Select b.ID from State b where b.Name = 'NY') where office.Name = 'HQ' -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Trinidad" <t@t.com> wrote in message news:1134175545.11a260ede2f81e60ff584565cb0628ef@roc.usenetexchange.com... > Hi > I do have a question about a query on update. I appreciate if anyone > could > help me. > > Here is the table I have: > > > Emp > > ID Lname Fname OfficeID > > 1 A Bobo 1 > > 2 B Coco 2 > > 3 C Jojo 1 > > > > Office > > ID Name StateID > > 1 HQ 1 > > 2 WareHouse 2 > > > > State > > ID Name > > 1 WA > > 2 CA > > > > I want to move the HQ from WA to NY. > > First I do this: > > > > insert into state > > (Name) > > values ('NY'); > > > > Then, I do this: > > Update office a > set a.StateID = (Select b.ID from State b where b.Name = 'NY') > where a.Name = 'HQ'; > > > > The database complains: "Operation must use an updateable query." > > > > How could I fix that? > > > > Thanks. > > this may be help.
Update office a set a.StateID = b.ID from State b where b.Name = 'NY' and a.name='HQ' Yifei Show quote "Trinidad" <t@t.com> wrote in message news:1134175545.11a260ede2f81e60ff584565cb0628ef@roc.usenetexchange.com... > Hi > I do have a question about a query on update. I appreciate if anyone could > help me. > > Here is the table I have: > > > Emp > > ID Lname Fname OfficeID > > 1 A Bobo 1 > > 2 B Coco 2 > > 3 C Jojo 1 > > > > Office > > ID Name StateID > > 1 HQ 1 > > 2 WareHouse 2 > > > > State > > ID Name > > 1 WA > > 2 CA > > > > I want to move the HQ from WA to NY. > > First I do this: > > > > insert into state > > (Name) > > values ('NY'); > > > > Then, I do this: > > Update office a > set a.StateID = (Select b.ID from State b where b.Name = 'NY') > where a.Name = 'HQ'; > > > > The database complains: "Operation must use an updateable query." > > > > How could I fix that? > > > > Thanks. > > "Trinidad" <t@t.com> wrote in message <snip>news:1134175545.11a260ede2f81e60ff584565cb0628ef@roc.usenetexchange.com... > Hi > I do have a question about a query on update. I appreciate if anyone could > help me. > Trinidad,> > > The database complains: "Operation must use an updateable query." > > How could I fix that? This is an MS Access error message that does not appear in SQL Server. In MS Access, you may not place a subquery after "SET =" in an Update query. You must use the syntax: UPDATE <table expression 1> AS <alias1> <join keyword> <table expression 2> AS <alias1> SET = <alias1>.<column1> And there should be no aggregation in the <table expressions> (MS Access is touchy about that, as well, in Update queries.) Sincerely, Chris O. "Chris2" <rainofsteel.NOTVA***@GETRIDOF.luminousrain.com> wrote in should bemessage news:F4idnRl3McvM0AHeRVn-vQ@comcast.com... > UPDATE <table expression 1> AS <alias1> > <join keyword> > <table expression 2> AS <alias1> <alias2> Sincerely, Chris O.  |
|||||||||||||||||||||||
Other interesting topics