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 |
|
harlingtonthewizard
Constraint Violating Yak Guru
352 Posts |
 Posted - 2009-06-24 : 21:08:26
|
| I am tring to update a table 'CommunicationsDetails' from a query and it is currently not working. I import multiple tables from a CSV file into a temp table. I then need to update the db tables with the data from the temp table. At the bottom is what I have so far.Here are my tables in the db.CommunicationsDetailsCommunicationID MultiCommTYpe CommMethod DefaultMultiCommConfig PhoneNumber IPAddr Port1 Port21 PSTN PSTN_DEFAULT False NULL 2000 5542 ISDN ISDN64 False NULL 2000 5543 ISDN ISDN128 False NULL 2000 5544 Network Network_DEFAULT True 172.20.201.5 2000 5545 PSTN PSTN_DEFAULT False NULL 2000 5546 ISDN ISDN64 False NULL 2000 5547 ISDN ISDN128 False NULL 2000 5548 Network Network_DEFAULT True 172.20.201.7 2000 5549 PSTN PSTN_DEFAULT False NULL 2000 55410 ISDN ISDN64 False NULL 2000 55411 ISDN ISDN128 False NULL 2000 55412 Network Network_DEFAULT True 172.20.201.8 2000 554SiteCommunicationsSiteID CommunicationsID2 12 22 32 43 53 63 73 84 94 104 114 12SiteDetailsSiteID DeviceID SiteName TransmitterType1 SystemMonitorSite SystemMonitor SystemMonitor2 2015 Bravo V31003 2017 Delta V31004 2018 Echo V3100This select will return what seems like the correct rows as shown below. I then need to update DefaultMultiCommConfig = uDefaultMultiCommConfig PhoneNumber = uPhoneNumber IPAddr = uIPAddr Port1 = uPort1 Port2 = uPort2 for each row. The unique fields are SiteName, MultiCommType and CommMethod as there will be four CommMethods for each site. SELECT * FROM ( SELECT DISTINCT SiteDetails.SiteName, #u.SiteName as uSiteName, CommunicationDetails.MultiCommType, #u.MultiCommType as uMultiCommType, CommunicationDetails.CommMethod, #u.CommMethod as uCommMethod, CommunicationDetails.DefaultMultiCommConfig, #u.DefaultMultiCommConfig as uDefaultMultiCommConfig, CommunicationDetails.PhoneNumber, #u.PhoneNumber as uPhoneNumber, CommunicationDetails.IPAddr, #u.IPAddr as uIPAddr, CommunicationDetails.Port1, #u.Port1 as uPort1, CommunicationDetails.Port2, #u.Port2 as uPort2 FROM CommunicationDetails INNER JOIN SiteCommunications ON CommunicationDetails.CommunicationID = SiteCommunications.CommunicationID INNER JOIN SiteDetails ON SiteCommunications.SiteID = SiteDetails.SiteID INNER JOIN #u ON SiteDetails.SiteName = #u.SiteName AND CommunicationDetails.MultiCommType = #u.MultiCommType AND CommunicationDetails.CommMethod = #u.CommMethod) as d WHERE SiteName = uSiteName AND MultiCommType = uMultiCommType AND CommMethod = uCommMethodSiteName uSiteName MultiCommTYpe uMultiCommTYpe CommMethod uCommMethod DefaultMultiCommConfig uDefaultMultiCommConfig PhoneNumber uPhoneNumber IPAddr uIPAddr Port1 uPort1 Port2 uPort2Bravo Bravo ISDN ISDN ISDN128 ISDN128 0 0 NULL NULL 2000 2001 554 554Bravo Bravo ISDN ISDN ISDN64 ISDN64 0 0 NULL NULL 2000 2001 554 554Bravo Bravo Network Network Network_DEFAULT Network_DEFAULT 1 1 172.20.201.5 172.20.201.50 2000 2001 554 554Bravo Bravo PSTN PSTN PSTN_DEFAULT PSTN_DEFAULT 0 0 100 NULL NULL 2000 2001 554 554Delta Delta ISDN ISDN ISDN128 ISDN128 0 0 NULL NULL 2000 2000 554 554Delta Delta ISDN ISDN ISDN64 ISDN64 0 0 NULL NULL 2000 2000 554 554Delta Delta Network Network Network_DEFAULT Network_DEFAULT 1 1 NULL 172.20.201.7 172.20.201.70 2000 2000 554 554Delta Delta PSTN PSTN PSTN_DEFAULT PSTN_DEFAULT 0 0 NULL NULL 2000 2000 554 554Echo Echo ISDN ISDN ISDN128 ISDN128 0 0 NULL NULL 2000 2000 554 554Echo Echo ISDN ISDN ISDN64 ISDN64 0 0 NULL NULL 2000 2000 554 554Echo Echo Network Network Network_DEFAULT Network_DEFAULT 1 1 NULL 172.20.201.8 172.20.201.80 2000 2000 554 554Echo Echo PSTN PSTN PSTN_DEFAULT PSTN_DEFAULT 0 0 NULL NULL 2000 2000 554 554IF I replace the select with the update like this I get the table as below which is incorrect:UPDATE CommunicationDetails SET CommunicationDetails.MultiCommType = d.uMultiCommType, CommunicationDetails.CommMethod = d.uCommMethod, CommunicationDetails.DefaultMultiCommConfig = d.uDefaultMultiCommConfig, CommunicationDetails.PhoneNumber = d.uPhoneNumber, CommunicationDetails.IPAddr = d.uIPAddr, CommunicationDetails.Port1 = d.uPort1, CommunicationDetails.Port2 = d.uPort2 FROM ( SELECT DISTINCT SiteDetails.SiteName, #u.SiteName as uSiteName, CommunicationDetails.MultiCommType, #u.MultiCommType as uMultiCommType, CommunicationDetails.CommMethod, #u.CommMethod as uCommMethod, CommunicationDetails.DefaultMultiCommConfig, #u.DefaultMultiCommConfig as uDefaultMultiCommConfig, CommunicationDetails.PhoneNumber, #u.PhoneNumber as uPhoneNumber, CommunicationDetails.IPAddr, #u.IPAddr as uIPAddr, CommunicationDetails.Port1, #u.Port1 as uPort1, CommunicationDetails.Port2, #u.Port2 as uPort2 FROM CommunicationDetails INNER JOIN SiteCommunications ON CommunicationDetails.CommunicationID = SiteCommunications.CommunicationID INNER JOIN SiteDetails ON SiteCommunications.SiteID = SiteDetails.SiteID INNER JOIN #u ON SiteDetails.SiteName = #u.SiteName AND CommunicationDetails.MultiCommType = #u.MultiCommType AND CommunicationDetails.CommMethod = #u.CommMethod) as d WHERE d.SiteName = d.uSiteName AND d.MultiCommType = d.uMultiCommType AND d.CommMethod = d.uCommMethodCommunicationsDetails (incorrect)CommunicationID MultiCommTYpe CommMethod DefaultMultiCommConfig PhoneNumber IPAddr Port1 Port21 ISDN ISDN128 False NULL 2001 5542 PSTN PSTN_DEFAULT False NULL 2000 5543 ISDN ISDN128 False NULL 2001 5544 PSTN PSTN_DEFAULT False NULL 2000 5545 ISDN ISDN128 False NULL 2001 5546 PSTN PSTN_DEFAULT False NULL 2000 5547 ISDN ISDN128 False NULL 2001 5548 PSTN PSTN_DEFAULT False NULL 2000 5549 ISDN ISDN128 False NULL 2001 55410 PSTN PSTN_DEFAULT False NULL 2000 55411 ISDN ISDN128 False NULL 2001 55412 PSTN PSTN_DEFAULT False NULL 2000 554CommunicationsDetails (should be)CommunicationID MultiCommTYpe CommMethod DefaultMultiCommConfig PhoneNumber IPAddr Port1 Port21 PSTN PSTN_DEFAULT False 100 2001 5542 ISDN ISDN64 False NULL 2001 5543 ISDN ISDN128 False NULL 2001 5544 Network Network_DEFAULT True 172.20.201.50 2001 5545 PSTN PSTN_DEFAULT False NULL 2000 5546 ISDN ISDN64 False NULL 2000 5547 ISDN ISDN128 False NULL 2000 5548 Network Network_DEFAULT True 172.20.201.70 2000 5549 PSTN PSTN_DEFAULT False NULL 2000 55410 ISDN ISDN64 False NULL 2000 55411 ISDN ISDN128 False NULL 2000 55412 Network Network_DEFAULT True 172.20.201.80 2000 554 |
|
|
|
|
|
|
|
Help with update