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.
| Author |
Topic |
|
sadpanda
Starting Member
4 Posts |
 Posted - 2010-10-25 : 11:08:04
|
| Hello, I'm working on an a update query that has me stumped. The main table involved will need to have records removed from the table. However, if the records are deleted, orphans will appear in another table. I've been given another table that shows which records will be deleted. If a record is to be deleted, it will have it's ID "wrapped" into a record that will continue to exist. Here's an example below. I have placed a new column in the table called InstallationID. So, for subsection 04000, I would have a value of 60 that would need to appear for records 77,80 and 95. For subsection 05000, I would need a value of 96 to show up for the InstallationID for 97, 98 and 99. ID GroupID InstallationID Action Description 60 04000 Null Existing Facility One 77 04000 Null Delete Null 80 04000 Null Delete Null 95 04000 Null Delete Null 96 05000 Null Existing Facility Two 97 05000 Null Delete Null 98 05000 Null Delete Null 99 05000 Null Delete NullMy table has over 10,000 records so I was wondering how could I structure a query to work with each subsection to update the InstallationID? Thanks for the help. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-10-25 : 11:14:43
|
| 1. so you have an update query that deletes records? a bit confusing, why does an update query need to delete records, smells like bad design2. what the heck does InstallationID do for you in the update query3. what is this table where you added InstallationID , is it an audit table?If you don't have the passion to help people, you have no passion |
 |
|
|
sadpanda
Starting Member
4 Posts |
Posted - 2010-10-25 : 11:23:28
|
| 1. The update query won't be deleting any records. It will be updating the InstallationID for a section. The Installation ID that I want for a section would be tagged as 'Existing'.2. InstallationID is a more descriptive column I've created for where I want to store the ID value. So, I'm trying to get a record that's tagged as "Existing" to be the master record that would update the InstallationID column for records that are tagged "Deleted". 3. Refer to 2.Again, my initial query is only concerned with upating records. No deletions will be occurring... |
|
|
|
sadpanda
Starting Member
4 Posts |
Posted - 2010-10-25 : 11:34:14
|
| I think this might help as to what I'm trying to do.Original table data.ID GroupID InstallationID Action Description60 04000 Null Existing Facility One77 04000 Null Delete Null80 04000 Null Delete Null95 04000 Null Delete Null96 05000 Null Existing Facility Two97 05000 Null Delete Null98 05000 Null Delete Null99 05000 Null Delete NullFinal result of what I'd like to see the data updated to.ID GroupID InstallationID Action Description60 04000 Null Existing Facility One77 04000 60 Delete Null80 04000 60 Delete Null95 04000 60 Delete Null96 05000 Null Existing Facility Two97 05000 96 Delete Null98 05000 96 Delete Null99 05000 96 Delete Null |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2010-10-25 : 12:15:19
|
| [code]-- *** Test Data ***CREATE TABLE #t( ID int NOT NULL ,GroupID varchar(20) NOT NULL ,InstallationID int NULL ,[Action] varchar(20) NOT NULL ,[Description] varchar(20) NULL)INSERT INTO #tSELECT 60, '04000', Null, 'Existing', 'Facility One'UNION ALL SELECT 77, '04000', Null, 'Delete', NullUNION ALL SELECT 80, '04000', Null, 'Delete', NullUNION ALL SELECT 95, '04000', Null, 'Delete', NullUNION ALL SELECT 96, '05000', Null, 'Existing', 'Facility Two'UNION ALL SELECT 97, '05000', Null, 'Delete', NullUNION ALL SELECT 98, '05000', Null, 'Delete', NullUNION ALL SELECT 99, '05000', Null, 'Delete', Null-- *** End Test Data ***UPDATE DSET InstallationID = MinGroupIDFROM( SELECT ID, GroupID, InstallationID ,MIN(ID) OVER (PARTITION BY GroupID) AS MinGroupID FROM #t) DWHERE ID <> MinGroupIDSELECT * FROM #t[/code] |
|
|
|
sadpanda
Starting Member
4 Posts |
Posted - 2010-10-25 : 13:12:19
|
| Thanks for the response back Ifor. I see that you are going for the minimium id for a group which is a great idea.The solution that ended up working for me was to create a new table and place the records marked as "Existing" in it. From there, I could join back to the table using the GroupID column. After joining my new table with the old table, I could then update the InstallationID column with the appropriate value.I guess the lesson learned here is to break things out into multiple tables to make things easier.OriginalTable:ID GroupID InstallationID Action Description60 04000 Null Existing Facility One77 04000 Null Delete Null80 04000 Null Delete Null95 04000 Null Delete Null96 05000 Null Existing Facility Two97 05000 Null Delete Null98 05000 Null Delete Null99 05000 Null Delete NullNewTable:ID GroupID InstallationID Action Description60 04000 Null Existing Facility One96 05000 Null Existing Facility TwoUpdate query:UPDATE OriginalTableSET InstallationID = NewTable.IDFROM NewTable RIGHT OUTER JOIN OriginalTable ON NewTable.GroupID = OriginalTable.GroupIDWHERE (OriginalTable.Action = N'Delete') AND (NewTable.Action = 'Existing') |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-10-26 : 13:11:42
|
quote:
The solution that ended up working for me was to create a new table and place the records [sic: rows are not records] marked as "Existing" in it.
Create a VIEW instead. It will always be correct. You are still thinking in terms of redundant PHYSICAL data storage (i.e. records and working files) and not the more abstract SQL model.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Update query - subsections