Updatable Union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updatable Union

Author

Topic

emills
Starting Member

3 Posts

Posted - 2002-10-18 : 14:29:54

I have the following SQL query in my view

select *
from tblOne
where location = 'CA'
Union
select *
from tblTwo
where location = 'CA'
Union
select *
from tblThree
where location = 'CA'

It works fine and returns what I want. But I need it to be updateable. I have since found that when using a Union that the resulting recordset is not updateable. Is there a way to re-write this query so that I can get the desired results and have it updateable? All tables are identical.

Thanks,

Eric

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-18 : 15:19:47

No. You need to update the base tables instead.

And if all of the tables are identical, why can't you combine them into one table? That would solve the problem.

emills
Starting Member

3 Posts

Posted - 2002-10-18 : 15:22:14

Thanks. That is what I thought.

I was trying to avoid combinig all of them. The application was created by someone else, and I am trying to chagne some of the original functionality. I was just trying to avoid doing a complete re-write. Oh well, looks like it's time to get writing.

Eric

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-18 : 21:14:29

You can add an instead of trigger if you are using SQL 2000. You would have to do the insertion yourself, and you could avoid re-writing the system for a little while longer. (or, better yet, buy time to just get the things that need rewritten, rewritten.)

Search the main site for an explanation of them. I remember a series of trigger articles a while back.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

emills
Starting Member

3 Posts

Posted - 2002-10-18 : 21:56:22

Thank you for that idea. But we are not yet on SQL 2000. But, at least learned about something new!

Really though, the design is not quite right, and I just need to sit down and take the time to re-design. It will cause less headaches down the road.

Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources