Mar 19, 2008
Hello All,
I have a database with multiple tables, each table with a subset of common fields I need to work with. The way the database was set up was a series of append queries that created a common table to combine the subset of fields. The problem with this is that I had to work with the common table but update the data in the parent tables and regenerate common table to keep everything up to date. To get around this I have created a union query to replace the common table but the data is not updatable. I have had a quick scan of the net and it seems union queries are not updateable by default. I understand the problem I have is a result of the database not being structured correctly however due to circumstances beyond my control, I am stuck with it. I have come across a couple of INSTEAD OF trigger examples but not sure quite how they work and if they can help me. Below is the SQL code for my union query so any help in making it updatable would be greatly appreciated.
Regards
Daz
SELECT [Belt Filter Press].Table, [Belt Filter Press].Plant, [Belt Filter Press].[New Tag], [Belt Filter Press].[New Comment],
[Belt Filter Press].[P&ID number], [Belt Filter Press].Purpose, [Belt Filter Press].[Motor Size], [Belt Filter Press].Starting, [Belt
Filter Press].New, [Belt Filter Press].Comment, [Belt Filter Press].Switchboard, [Belt Filter Press].PLC, [Belt Filter
Press].[PLC Interface], [Belt Filter Press].ACH, [Belt Filter Press].IL, [Belt Filter Press].ZSO, [Belt Filter Press].WIO, [Belt
Filter Press].ZSP, [Belt Filter Press].PSL, [Belt Filter Press].FSL, [Belt Filter Press].TSH, [Belt Filter Press].MSX, [Belt Filter
Press].SSL
FROM [Belt Filter Press];
UNION SELECT Bins.Table, Bins.Plant, Bins.NewTag, Bins.[New Comment], Bins.[P&ID number], Bins.Purpose, Bins.[Motor
Size], Bins.Starting, Bins.New, Bins.Comment, Bins.Switchboard, Bins.PLC, Bins.[PLC Interface], Bins.ACH, Bins.IL,
Bins.ZSO, Bins.WIO, Bins.ZSP, Bins.PSL, Bins.FSL, Bins.TSH, Bins.MSX, Bins.SSL
FROM Bins;
UNION SELECT [Chemical Dosing].Table, [Chemical Dosing].Plant, [Chemical Dosing].[New Tag], [Chemical Dosing].[New
Comment], [Chemical Dosing].[P&ID number], [Chemical Dosing].Purpose, [Chemical Dosing].[Motor Size], [Chemical
Dosing].Starting, [Chemical Dosing].New, [Chemical Dosing].Comment, [Chemical Dosing].Switchboard, [Chemical
Dosing].PLC, [Chemical Dosing].[PLC Interface], [Chemical Dosing].ACH, [Chemical Dosing].IL, [Chemical Dosing].ZSO,
[Chemical Dosing].WIO, [Chemical Dosing].ZSP, [Chemical Dosing].PSL, [Chemical Dosing].FSL, [Chemical Dosing].TSH,
[Chemical Dosing].MSX, [Chemical Dosing].SSL
FROM [Chemical Dosing];
UNION SELECT Clarifiers.Table, Clarifiers.Plant, Clarifiers.[New Tag], Clarifiers.[New Comment], Clarifiers.[P&ID number],
Clarifiers.Purpose, Clarifiers.[Motor Size], Clarifiers.Starting, Clarifiers.New, Clarifiers.Comment, Clarifiers.Switchboard,
Clarifiers.PLC, Clarifiers.[PLC Interface], Clarifiers.ACH, Clarifiers.IL, Clarifiers.ZSO, Clarifiers.WIO, Clarifiers.ZSP,
Clarifiers.PSL, Clarifiers.FSL, Clarifiers.TSH, Clarifiers.MSX, Clarifiers.SSL
FROM Clarifiers;
UNION SELECT Filters.Table, Filters.Plant, Filters.[New Tag], Filters.[New Comment], Filters.[P&ID number], Filters.Purpose,
Filters.[Motor Size], Filters.Starting, Filters.New, Filters.Comment, Filters.Switchboard, Filters.PLC, Filters.[PLC Interface],
Filters.ACH, Filters.IL, Filters.ZSO, Filters.WIO, Filters.ZSP, Filters.PSL, Filters.FSL, Filters.TSH, Filters.MSX, Filters.SSL
FROM Filters;
UNION SELECT [Gravity Drainage Deck].Table, [Gravity Drainage Deck].Plant, [Gravity Drainage Deck].[New Tag], [Gravity
Drainage Deck].[New Comment], [Gravity Drainage Deck].[P&ID number], [Gravity Drainage Deck].Purpose, [Gravity Drainage
Deck].[Motor Size], [Gravity Drainage Deck].Starting, [Gravity Drainage Deck].New, [Gravity Drainage Deck].Comment,
[Gravity Drainage Deck].Switchboard, [Gravity Drainage Deck].PLC, [Gravity Drainage Deck].[PLC Interface], [Gravity Drainage
Deck].ACH, [Gravity Drainage Deck].IL, [Gravity Drainage Deck].ZSO, [Gravity Drainage Deck].WIO, [Gravity Drainage
Deck].ZSP, [Gravity Drainage Deck].PSL, [Gravity Drainage Deck].FSL, [Gravity Drainage Deck].TSH, [Gravity Drainage
Deck].MSX, [Gravity Drainage Deck].SSL
FROM [Gravity Drainage Deck];
UNION SELECT [Grit Classifiers].Table, [Grit Classifiers].Plant, [Grit Classifiers].[New Tag], [Grit Classifiers].[New Comment],
[Grit Classifiers].[P&ID number], [Grit Classifiers].Purpose, [Grit Classifiers].[Motor Size], [Grit Classifiers].Starting, [Grit
Classifiers].New, [Grit Classifiers].Comment, [Grit Classifiers].Switchboard, [Grit Classifiers].PLC, [Grit Classifiers].[PLC
Interface], [Grit Classifiers].ACH, [Grit Classifiers].IL, [Grit Classifiers].ZSO, [Grit Classifiers].WIO, [Grit Classifiers].ZSP, [Grit
Classifiers].PSL, [Grit Classifiers].FSL, [Grit Classifiers].TSH, [Grit Classifiers].MSX, [Grit Classifiers].SSL
FROM [Grit Classifiers];
UNION SELECT Instruments.Table, Instruments.Plant, Instruments.[New Tag], Instruments.[New Comment],
Instruments.[P&ID Number], Instruments.Purpose, Instruments.[Motor Size], Instruments.Starting, Instruments.New,
Instruments.Comment, Instruments.Switchboard, Instruments.PLC, Instruments.[PLC Interface], Instruments.ACH,
Instruments.IL, Instruments.ZSO, Instruments.WIO, Instruments.ZSP, Instruments.PSL, Instruments.FSL, Instruments.TSH,
Instruments.MSX, Instruments.SSL
FROM Instruments;
UNION SELECT Mixer.Table, Mixer.Plant, Mixer.[New Tag], Mixer.[New Comment], Mixer.[P&ID number], Mixer.Purpose,
Mixer.[Motor Size], Mixer.Starting, Mixer.New, Mixer.Comment, Mixer.Switchboard, Mixer.PLC, Mixer.[PLC Interface],
Mixer.ACH, Mixer.IL, Mixer.ZSO, Mixer.WIO, Mixer.ZSP, Mixer.PSL, Mixer.FSL, Mixer.TSH, Mixer.MSX, Mixer.SSL
FROM Mixer;
UNION SELECT [Odour Control].Table, [Odour Control].Plant, [Odour Control].[New Tag], [Odour Control].[New Comment],
[Odour Control].[P&ID number], [Odour Control].Purpose, [Odour Control].[Motor Size], [Odour Control].Starting, [Odour
Control].New, [Odour Control].Comment, [Odour Control].Switchboard, [Odour Control].PLC, [Odour Control].[PLC Interface],
[Odour Control].ACH, [Odour Control].IL, [Odour Control].ZSO, [Odour Control].WIO, [Odour Control].ZSP, [Odour
Control].PSL, [Odour Control].FSL, [Odour Control].TSH, [Odour Control].MSX, [Odour Control].SSL
FROM [Odour Control];
UNION SELECT [Pump List].Table, [Pump List].Plant, [Pump List].[New Tag], [Pump List].[New Comment], [Pump List].[P&ID
number], [Pump List].Purpose, [Pump List].[Motor Size], [Pump List].Starting, [Pump List].New, [Pump List].Comment,
[Pump List].Switchboard, [Pump List].PLC, [Pump List].[PLC Interface], [Pump List].ACH, [Pump List].IL, [Pump List].ZSO,
[Pump List].WIO, [Pump List].ZSP, [Pump List].PSL, [Pump List].FSL, [Pump List].TSH, [Pump List].MSX, [Pump List].SSL
FROM [Pump List];
UNION SELECT [Safety Shower].Table, [Safety Shower].Plant, [Safety Shower].[New Tag], [Safety Shower].[New Comment],
[Safety Shower].[P&ID number], [Safety Shower].Purpose, [Safety Shower].[Motor Size], [Safety Shower].Starting, [Safety
Shower].New, [Safety Shower].Comment, [Safety Shower].Switchboard, [Safety Shower].PLC, [Safety Shower].[PLC
Interface], [Safety Shower].ACH, [Safety Shower].IL, [Safety Shower].ZSO, [Safety Shower].WIO, [Safety Shower].ZSP,
[Safety Shower].PSL, [Safety Shower].FSL, [Safety Shower].TSH, [Safety Shower].MSX, [Safety Shower].SSL
FROM [Safety Shower];
UNION SELECT [Screenings Equipment].Table, [Screenings Equipment].Plant, [Screenings Equipment].[New Tag],
[Screenings Equipment].[New Comment], [Screenings Equipment].[P&ID number], [Screenings Equipment].Purpose,
[Screenings Equipment].[Motor Size], [Screenings Equipment].Starting, [Screenings Equipment].New, [Screenings
Equipment].Comment, [Screenings Equipment].Switchboard, [Screenings Equipment].PLC, [Screenings Equipment].[PLC
Interface], [Screenings Equipment].ACH, [Screenings Equipment].IL, [Screenings Equipment].ZSO, [Screenings
Equipment].WIO, [Screenings Equipment].ZSP, [Screenings Equipment].PSL, [Screenings Equipment].FSL, [Screenings
Equipment].TSH, [Screenings Equipment].MSX, [Screenings Equipment].SSL
FROM [Screenings Equipment];
UNION SELECT Screens.Table, Screens.Plant, Screens.[New Tag], Screens.[New Comment], Screens.[P&ID number],
Screens.Purpose, Screens.[Motor SIze], Screens.Starting, Screens.New, Screens.Comment, Screens.Switchboard,
Screens.PLC, Screens.[PLC Interface], Screens.ACH, Screens.IL, Screens.ZSO, Screens.WIO, Screens.ZSP, Screens.PSL,
Screens.FSL, Screens.TSH, Screens.MSX, Screens.SSL
FROM Screens;
UNION SELECT [Surface Aerator].Table, [Surface Aerator].Plant, [Surface Aerator].[New Tag], [Surface Aerator].[New
Comment], [Surface Aerator].[P&ID number], [Surface Aerator].Purpose, [Surface Aerator].[Motor Size], [Surface
Aerator].Starting, [Surface Aerator].New, [Surface Aerator].Comment, [Surface Aerator].Switchboard, [Surface Aerator].PLC,
[Surface Aerator].[PLC Interface], [Surface Aerator].ACH, [Surface Aerator].IL, [Surface Aerator].ZSO, [Surface Aerator].WIO,
[Surface Aerator].ZSP, [Surface Aerator].PSL, [Surface Aerator].FSL, [Surface Aerator].TSH, [Surface Aerator].MSX, [Surface
Aerator].SSL
FROM [Surface Aerator];
UNION SELECT Tanks.Table, Tanks.Plant, Tanks.[New Tag], Tanks.[New Comment], Tanks.[P&ID number], Tanks.Purpose,
Tanks.[Motor Size], Tanks.Starting, Tanks.New, Tanks.Comment, Tanks.Switchboard, Tanks.PLC, Tanks.[PLC Interface],
Tanks.ACH, Tanks.IL, Tanks.ZSO, Tanks.WIO, Tanks.ZSP, Tanks.PSL, Tanks.FSL, Tanks.TSH, Tanks.MSX, Tanks.SSL
FROM Tanks;
UNION SELECT [UV System].Table, [UV System].Plant, [UV System].[New Tag], [UV System].[New Comment], [UV
System].[P&ID number], [UV System].Purpose, [UV System].[Motor Size], [UV System].Starting, [UV System].New, [UV
System].Comment, [UV System].Switchboard, [UV System].PLC, [UV System].[PLC Interface], [UV System].ACH, [UV
System].IL, [UV System].ZSO, [UV System].WIO, [UV System].ZSP, [UV System].PSL, [UV System].FSL, [UV
System].TSH, [UV System].MSX, [UV System].SSL
FROM [UV System];
UNION SELECT Valves.Table, Valves.Plant, Valves.[New Tag], Valves.[New Comment], Valves.[P&ID number],
Valves.Purpose, Valves.[Motor Size], Valves.Starting, Valves.New, Valves.Comment, Valves.Switchboard, Valves.PLC,
Valves.[PLC Interface], Valves.ACH, Valves.IL, Valves.ZSO, Valves.WIO, Valves.ZSP, Valves.PSL, Valves.FSL, Valves.TSH,
Valves.MSX, Valves.SSL
FROM Valves;
View 1 Replies
View Related