Update with unionALL - 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

General SQL Server Forums

New to SQL Server Programming

Update with unionALL

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-15 : 17:22:49

Hi Guys,

I have a Table_Common which has 4columns. col1, col2, col3, col4
I have other 6 tables (populated) and I used insert into to populate Table_common.

I realised I should have used update table_common so that if there is update in lets say, table5, it should automatically update the common table.

I used:
Update Table_common (col1,col2,col3,col4)
select col1,col2,col3,col4 from table1
union all
select col1,col2,col3,col4 from table1

and so on....

Its not working.
Suggest me a different approach/query.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 20:05:13

The question is not completely clear to me, but from what I understand,

1. you have a TABLE_COMMON and then another SIX tables.
2. If a row is inserted or updated in one of the six tables, you want to do an insert or update in the TABLE_COMMON.

Is that what you are trying to do? If that is the case, the most reliable way to achieve it is using triggers. If that is not what you are trying to do, can you describe? Brett's blog here may help.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-15 : 20:26:25

Okay, That Table_common is basically UNION ALL of the other six tables.

There is insert in one of the six tables.
So, I wanted to update the Table_common as well.

I figured another way.
Drop Table Table_common
Create table Table_common
then Union ALL other six tables.

This way, table_common will get the updated values from six tables.

Any other way ??

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 20:40:42

Don't do that, dropping and recreating the common table for every insert would be very very inefficient. If all you want to do is to have a common "table" that has all the data from the other six tables, you can create a view. That would be something like this:


Create view dbo.CommonView
as
select col1,col2,col3,col4 from table1
union all
select col1, col2, col3,col4 from table2
union all
select col1,col2,col3,col4 from table3 --etc.

Then you can select form the CommonView just like you would from the table.

If you do want to create and populate a separate physical common table, use triggers. Here is an example of how to do it:

-- Sample tables
create table tmp1 (col1 int, col2 int, col3 int);
create table tmp2 (col1 int, col2 int, col3 int);

-- Sample Common table
create table commonTable (col1 int, col2 int, col3 int);
GO

-- Insert trigger for first table.
create trigger tmp1ToCommon on tmp1 for insert
as
insert into commonTable select col1,col2, col3 from INSERTED;
GO

-- Insert trigger for Second table table.
create trigger tmp2ToCommon on tmp2 for insert
as
insert into commonTable select col1,col2, col3 from INSERTED;
GO

-- Insert sample data int tmp1 and tmp2;
insert into tmp1 values (1,2,3);
insert into tmp2 values (11,21,31);

-- See what is in commonTable;
select * from commonTable;

-- Cleanup;
drop table tmp1;
drop table tmp2
drop table commonTable;

What is the purpose of the common table? Is it so you can have a simple table from which to select, or is it for auditing purposes, or something else? Depending what your answer is, one of the above approaches, or something else entirely different may be the appropriate thing to do. So can you describe what the business goal is?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-16 : 00:34:28

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.
NEVER put “tbl”, “table” or other meta data into a table name. “Common” is an adjective and not a noun. Let's call it Foobar, which is more descriptive.

>> I have other 6 tables (populated) and I used insert into to populate Foobar.<<

NO, NO, NO! The kludge is to put into a VIEW with UNIONs. The right answer is that you seem to be attribute splitting. Would you split the “Personnel” table into “Male_personnel” and “Female_Personnel” then union them together?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources