Solved

Help with query against AdventureWorks database - Delete Customers

Posted on 2010-08-28
17
785 Views
Last Modified: 2012-05-10
Need a hand with this query:

Delete the customer(s) from the database who are from the state of Maine ('WA').

Thanks!
0
Comment
Question by:John500
  • 9
  • 3
  • 2
  • +1
17 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33551240
delete
from customer c
inner join CustomerAddress ca on c.CustomerID=ca.CustomerID and ca.AddressType='Main Office'
inner join Address a on a.AddressID=ca.AddressID
where a.StateProvince = 'Maine'
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33551255
Minor correction:

delete c
from customer c
inner join CustomerAddress ca on c.CustomerID=ca.CustomerID and ca.AddressType='Main Office'
inner join Address a on a.AddressID=ca.AddressID
where a.StateProvince = 'Maine'

To avoid the need to specify the table to delete you can use exists.

delete
from customer c
where exists (
   select top(1) null
   from CustomerAddress ca
   inner join Address a on a.AddressID=ca.AddressID
   where a.StateProvince = 'Maine'
   and c.CustomerID=ca.CustomerID
   and ca.AddressType='Main Office'
);
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 33552257
Thanks Kevin.
That wasn't all of it though, the foreign key constraint will prevent the delete.

The removal has to be CustomerAddress, then Customer and Address.
I have left the Address alone because while it is the Main Office for a customer, it could also be a ship to or bill to address linked from the SalesOrderHeader table.
declare @c table (CustomerID int)
;
insert @c select c.CustomerID
from sales.customer c
inner join sales.CustomerAddress ca on c.CustomerID=ca.CustomerID and ca.AddressType='Main Office'
inner join sales.Address a on a.AddressID=ca.AddressID
where c.CustomerID=30968
;
delete ca
from sales.CustomerAddress ca
where ca.customerID in (select CustomerID from @c)
;
delete sales.Customer
where customerID in (select CustomerID from @c)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:John500
ID: 33554107
... I modified your code (below) to use the StateProvinceID.  I'm just trying to figure out the syntax for deleting with cascade.  

Any input there?  Thanks
declare @c table (CustomerID int)
;
insert @c select c.CustomerID
FROM sales.customer c
INNER JOIN sales.CustomerAddress ca ON c.CustomerID=ca.CustomerID
INNER JOIN person.address pa ON ca.addressID = pa.addressid
WHERE pa.StateProvinceID = 79
;
on delete cascade ca
from sales.CustomerAddress ca
where ca.customerID in (select CustomerID from @c)
;
on delete cascade sales.Customer
where customerID in (select CustomerID from @c)
-------------------------------------------------------

results of @c table

cust id  StateProvinceID 

1	79
2	79
2	79
19	79
19	79
20	79
37	79
73	79
92	79
109	79
127	79
128	79
145	79
146	79
199	79
200	79
217	79
218	79
235	79
236	79
253	79
254	79
254	79
271	79
272	79
289	79
290	79
307	79
308	79
325	79
325	79
326	79
343	79
344	79
361	79
362	79
397	79
415	79
416	79
433	79
469	79
470	79
505	79
542	79
577	79
578	79
596	79
605	79
621	79
668	79
669	79
11012	79
11014	79
11022	79
11040	79
11063	79
11067	79
11128	79
11129	79
11137	79
11144	79
11152	79
11153	79
11157	79
11166	79
11167	79
11170	79
11172	79
11182	79
11187	79
11189	79
11192	79
11195	79
11197	79
11205	79
11206	79
11207	79
11213	79
11218	79
11224	79
11225	79
11231	79
11232	79
11235	79
11236	79
11254	79
11255	79
11256	79
11263	79
11264	79
11265	79
11266	79
11274	79
11278	79
11279	79
11279	79
11280	79
11282	79
11293	79
11294	79
11296	79
11301	79
11303	79
11311	79
11312	79
11313	79
11314	79
11315	79
11318	79
11321	79
11322	79
11325	79
11508	79
11514	79
11521	79
11532	79
11532	79
11620	79
11621	79
11622	79
11630	79
11633	79
11634	79
11643	79
11644	79
11646	79
11663	79
11667	79
11669	79
11673	79
11675	79
11679	79
11680	79
11681	79
11682	79
11684	79
11688	79
11689	79
11690	79
11696	79
11699	79
11713	79
11722	79
11725	79
11726	79
11729	79
11730	79
11735	79
11745	79
11746	79
11747	79
11770	79
11771	79
11772	79
11778	79
11780	79
11785	79
11786	79
11791	79
11798	79
11799	79
11803	79
11804	79
11810	79
11812	79
11815	79
11819	79
11829	79
11832	79
11836	79
11842	79
11848	79
11857	79
11859	79
11862	79
11863	79
11864	79
11870	79
11871	79
11878	79
11882	79
11883	79
11884	79
11889	79
11925	79
11927	79
11933	79
11934	79
11936	79
11957	79
11958	79
11960	79
11972	79
11973	79
11981	79
12024	79
12027	79
12030	79
12033	79
12041	79
12042	79
12057	79
12059	79
12063	79
12065	79
12075	79
12078	79
12083	79
12092	79
12098	79
12099	79
12108	79
12109	79
12117	79
12120	79
12139	79
12143	79
12144	79
12145	79
12155	79
12161	79
12167	79
12168	79
12185	79
12192	79
12199	79
12207	79
12208	79
12364	79
12365	79
12366	79
12413	79
12419	79
12422	79
12423	79
12424	79
12425	79
12434	79
12436	79
12452	79
12454	79
12456	79
12528	79
12533	79
12707	79
12757	79
12758	79
12759	79
12764	79
12767	79
12769	79
12772	79
12773	79
12776	79
12777	79
12779	79
12780	79
12787	79
12792	79
12796	79
12875	79
12877	79
12905	79
12906	79
12907	79
12908	79
12913	79
12915	79
12920	79
12930	79
12937	79
12938	79
12941	79
12946	79
12953	79
12954	79
12958	79
12973	79
12979	79
12980	79
13014	79
13016	79
13028	79
13029	79
13053	79
13054	79
13062	79
13063	79
13064	79
13067	79
13069	79
13083	79
13085	79
13086	79
13088	79
13093	79
13098	79
13153	79
13154	79
13172	79
13180	79
13182	79
13183	79
13188	79
13196	79
13203	79
13213	79
13214	79
13224	79
13226	79
13230	79
13233	79
13234	79
13244	79
13248	79
13269	79
13274	79
13275	79
13276	79
13280	79
13292	79
13294	79
13300	79
13301	79
13304	79
13305	79
13308	79
13310	79
13311	79
13311	79
13319	79
13322	79
13327	79
13337	79
13344	79
13351	79
13353	79
13354	79
13355	79
13356	79
13367	79
13368	79
13369	79
13372	79
13379	79
13384	79
13385	79
13395	79
13396	79
13397	79
13398	79
13419	79
13425	79
13431	79
13432	79
13436	79
13437	79
13438	79
13441	79
13442	79
13449	79
13456	79
13458	79
13462	79
13467	79
13469	79
13478	79
13483	79
13488	79
13653	79
13654	79
13655	79
13658	79
13659	79
13703	79
13704	79
13713	79
13718	79
13721	79
13722	79
13729	79
13730	79
13733	79
13735	79
13739	79
13740	79
13742	79
13744	79
13747	79
13748	79
13750	79
13754	79
13757	79
13763	79
13861	79
13867	79
13872	79
13880	79
13881	79
13884	79
13885	79
13888	79
13894	79
13895	79
13896	79
13897	79
13899	79
13900	79
13906	79
13907	79
13910	79
13911	79
13926	79
13928	79
13931	79
13934	79
13945	79
13950	79
13951	79
13976	79
13977	79
13978	79
13979	79
13989	79
13991	79
14002	79
14021	79
14022	79
14025	79
14026	79
14027	79
14028	79
14042	79
14083	79
14085	79
14092	79
14097	79
14098	79
14113	79
14116	79
14117	79
14121	79
14240	79
14242	79
14247	79
14255	79
14255	79
14256	79
14266	79
14271	79
14272	79
14280	79
14284	79
14333	79
14334	79
14335	79
14342	79
14352	79
14358	79
14361	79
14366	79
14372	79
14375	79
14376	79
14377	79
14384	79
14391	79
14392	79
14394	79
14396	79
14397	79
14398	79
14402	79
14404	79
14405	79
14417	79
14420	79
14430	79
14432	79
14438	79
14439	79
14447	79
14449	79
14452	79
14453	79
14456	79
14460	79
14461	79
14467	79
14468	79
14469	79
14475	79
14476	79
14481	79
14492	79
14503	79
14510	79
14511	79
14512	79
14520	79
14558	79
14563	79
14569	79
14570	79
14575	79
14576	79
14588	79
14592	79
14600	79
14608	79
14610	79
14613	79
14617	79
14618	79
14619	79
14624	79
14625	79
14629	79
14637	79
14643	79
14650	79
14654	79
14656	79
14657	79
14721	79
14722	79
14726	79
14728	79
14732	79
14737	79
14743	79
14744	79
14751	79
14754	79
14878	79
14881	79
14887	79
14888	79
14898	79
14905	79
14908	79
14916	79
14917	79
14918	79
14922	79
15009	79
15009	79
15012	79
15023	79
15159	79
15163	79
15167	79
15168	79
15188	79
15189	79
15247	79
15248	79
15257	79
15258	79
15269	79
15270	79
15271	79
15272	79
15287	79
15290	79
15294	79
15297	79
15298	79
15300	79
15306	79
15311	79
15315	79
15320	79
15322	79
15325	79
15330	79
15332	79
15335	79
15348	79
15350	79
15351	79
15359	79
15369	79
15371	79
15372	79
15373	79
15380	79
15381	79
15386	79
15396	79
15407	79
15407	79
15409	79
15415	79
15420	79
15421	79
15423	79
15430	79
15472	79
15479	79
15489	79
15492	79
15499	79
15503	79
15504	79
15508	79
15509	79
15512	79
15512	79
15513	79
15514	79
15515	79
15523	79
15529	79
15531	79
15531	79
15550	79
15551	79
15552	79
15558	79
15560	79
15562	79
15563	79
15565	79
15573	79
15737	79
15743	79
15744	79
15745	79
15746	79
15749	79
15763	79
15774	79
15775	79
15777	79
15778	79
15778	79
15779	79
15782	79
15818	79
15819	79
15820	79
15825	79
15827	79
15834	79
15837	79
15842	79
15843	79
15849	79
15851	79
15860	79
15867	79
15869	79
15871	79
15880	79
15885	79
15887	79
15888	79
15892	79
15893	79
15894	79
15898	79
15901	79
15902	79
15907	79
15911	79
15914	79
15918	79
15934	79
15937	79
15938	79
15939	79
15944	79
15945	79
15951	79
15952	79
15958	79
15959	79
15963	79
15967	79
15971	79
15973	79
15975	79
15976	79
15977	79
15978	79
15979	79
15980	79
15987	79
15991	79
15994	79
15999	79
16004	79
16007	79
16008	79
16022	79
16024	79
16062	79
16067	79
16068	79
16073	79
16074	79
16075	79
16076	79
16082	79
16092	79
16099	79
16104	79
16107	79
16108	79
16117	79
16136	79
16137	79
16142	79
16144	79
16145	79
16146	79
16151	79
16153	79
16211	79
16214	79
16215	79
16216	79
16218	79
16218	79
16221	79
16226	79
16229	79
16231	79
16232	79
16237	79
16246	79
16248	79
16333	79
16353	79
16354	79
16360	79
16368	79
16369	79
16373	79
16376	79
16381	79
16382	79
16477	79
16498	79
16499	79
16500	79
16501	79
16642	79
16651	79
16653	79
16669	79
16670	79
16672	79
16716	79
16722	79
16725	79
16727	79
16732	79
16735	79
16736	79
16737	79
16751	79
16755	79
16756	79
16761	79
16762	79
16765	79
16774	79
16776	79
16780	79
16782	79
16783	79
16785	79
16789	79
16794	79
16796	79
16797	79
16801	79
16802	79
16803	79
16811	79
16819	79
16832	79
16835	79
16850	79
16856	79
16857	79
16858	79
16859	79
16864	79
16877	79
16884	79
16890	79
16893	79
16899	79
16908	79
16911	79
16912	79
16914	79
16915	79
16918	79
16922	79
16923	79
16951	79
16954	79
16956	79
16957	79
16970	79
16971	79
16974	79
16978	79
16980	79
16989	79
16991	79
16998	79
17001	79
17015	79
17017	79
17020	79
17028	79
17035	79
17040	79
17041	79
17042	79
17043	79
17044	79
17118	79
17121	79
17124	79
17127	79
17131	79
17132	79
17133	79
17134	79
17137	79
17140	79
17144	79
17149	79
17151	79
17158	79
17159	79
17160	79
17161	79
17167	79
17168	79
17180	79
17261	79
17264	79
17266	79
17268	79
17269	79
17271	79
17274	79
17276	79
17341	79
17342	79
17343	79
17349	79
17357	79
17359	79
17361	79
17362	79
17365	79
17368	79
17376	79
17389	79
17391	79
17392	79
17405	79
17413	79
17414	79
17423	79
17430	79
17433	79
17438	79
17439	79
17443	79
17444	79
17449	79
17451	79
17452	79
17456	79
17457	79
17461	79
17465	79
17471	79
17473	79
17477	79
17485	79
17487	79
17488	79
17505	79
17516	79
17518	79
17530	79
17533	79
17534	79
17535	79
17541	79
17544	79
17545	79
17560	79
17564	79
17565	79
17570	79
17575	79
17583	79
17587	79
17588	79
17589	79
17596	79
17597	79
17599	79
17600	79
17604	79
17608	79
17612	79
17615	79
17616	79
17618	79
17659	79
17669	79
17672	79
17675	79
17679	79
17682	79
17687	79
17688	79
17689	79
17692	79
17693	79
17862	79
17864	79
17872	79
18049	79
18059	79
18060	79
18074	79
18082	79
18083	79
18083	79
18084	79
18097	79
18104	79
18115	79
18117	79
18121	79
18224	79
18225	79
18226	79
18227	79
18233	79
18234	79
18316	79
18318	79
18323	79
18353	79
18357	79
18364	79
18365	79
18371	79
18374	79
18383	79
18384	79
18394	79
18396	79
18402	79
18403	79
18404	79
18408	79
18429	79
18435	79
18439	79
18442	79
18471	79
18472	79
18475	79
18479	79
18499	79
18501	79
18516	79
18517	79
18518	79
18527	79
18530	79
18532	79
18534	79
18538	79
18542	79
18545	79
18551	79
18558	79
18559	79
18582	79
18583	79
18584	79
18587	79
18588	79
18589	79
18593	79
18593	79
18599	79
18605	79
18606	79
18638	79
18640	79
18645	79
18651	79
18652	79
18653	79
18655	79
18658	79
18661	79
18664	79
18665	79
18666	79
18676	79
18679	79
18688	79
18689	79
18689	79
18690	79
18757	79
18765	79
18787	79
18789	79
18790	79
18791	79
18797	79
18797	79
18802	79
18803	79
18810	79
18813	79
18814	79
18815	79
18817	79
18828	79
18829	79
18834	79
18836	79
18840	79
18844	79
18845	79
18846	79
18849	79
18854	79
18857	79
18860	79
18861	79
18865	79
18872	79
18874	79
18886	79
18887	79
18888	79
18889	79
18890	79
18905	79
18911	79
18912	79
18918	79
18921	79
18923	79
18933	79
18934	79
18943	79
18957	79
18963	79
18969	79
18982	79
18984	79
18985	79
18990	79
18991	79
18994	79
18997	79
19002	79
19005	79
19008	79
19021	79
19024	79
19108	79
19119	79
19125	79
19126	79
19131	79
19135	79
19136	79
19137	79
19139	79
19141	79
19143	79
19149	79
19151	79
19154	79
19157	79
19169	79
19170	79
19202	79
19208	79
19209	79
19215	79
19219	79
19222	79
19228	79
19236	79
19237	79
19238	79
19239	79
19246	79
19410	79
19412	79
19413	79
19529	79
19538	79
19540	79
19549	79
19551	79
19552	79
19637	79
19649	79
19657	79
19663	79
19668	79
19669	79
19678	79
19682	79
19684	79
19689	79
19691	79
19694	79
19699	79
19701	79
19706	79
19712	79
19713	79
19721	79
19727	79
19728	79
19733	79
19737	79
19738	79
19740	79
19741	79
19742	79
19743	79
19754	79
19755	79
19774	79
19803	79
19813	79
19815	79
19824	79
19834	79
19842	79
19845	79
19848	79
19852	79
19860	79
19863	79
19871	79
19875	79
19885	79
19886	79
19891	79
19893	79
19894	79
19980	79
19985	79
19993	79
19996	79
20001	79
20018	79
20019	79
20025	79
20026	79
20032	79
20039	79
20066	79
20071	79
20072	79
20073	79
20080	79
20081	79
20088	79
20089	79
20102	79
20107	79
20108	79
20113	79
20114	79
20119	79
20120	79
20125	79
20190	79
20195	79
20200	79
20272	79
20274	79
20276	79
20281	79
20281	79
20285	79
20295	79
20301	79
20304	79
20315	79
20319	79
20322	79
20323	79
20331	79
20332	79
20333	79
20342	79
20343	79
20357	79
20358	79
20361	79
20362	79
20365	79
20370	79
20378	79
20381	79
20383	79
20385	79
20388	79
20389	79
20390	79
20394	79
20399	79
20408	79
20409	79
20410	79
20432	79
20467	79
20471	79
20482	79
20487	79
20488	79
20489	79
20491	79
20501	79
20506	79
20507	79
20513	79
20514	79
20520	79
20521	79
20529	79
20538	79
20539	79
20544	79
20545	79
20633	79
20634	79
20635	79
20645	79
20652	79
20660	79
20664	79
20667	79
20674	79
20688	79
20689	79
20693	79
20732	79
20733	79
20735	79
20738	79
20739	79
20747	79
20748	79
20749	79
20752	79
20753	79
20758	79
20767	79
20769	79
20770	79
20775	79
20776	79
20778	79
20787	79
21141	79
21144	79
21146	79
21147	79
21150	79
21158	79
21161	79
21236	79
21247	79
21253	79
21260	79
21264	79
21267	79
21270	79
21273	79
21280	79
21289	79
21291	79
21296	79
21299	79
21304	79
21314	79
21319	79
21320	79
21321	79
21323	79
21335	79
21337	79
21340	79
21353	79
21353	79
21355	79
21357	79
21395	79
21396	79
21397	79
21410	79
21413	79
21415	79
21421	79
21427	79
21428	79
21432	79
21436	79
21439	79
21443	79
21445	79
21455	79
21467	79
21473	79
21474	79
21481	79
21483	79
21484	79
21485	79
21491	79
21495	79
21500	79
21503	79
21504	79
21574	79
21578	79
21584	79
21590	79
21592	79
21598	79
21602	79
21603	79
21605	79
21606	79
21607	79
21608	79
21616	79
21623	79
21624	79
21626	79
21628	79
21630	79
21633	79
21634	79
21639	79
21645	79
21647	79
21651	79
21657	79
21685	79
21689	79
21692	79
21696	79
21704	79
21706	79
21707	79
21708	79
21714	79
21715	79
21716	79
21721	79
21728	79
21729	79
21733	79
21757	79
21758	79
21762	79
21763	79
21770	79
21776	79
21778	79
21781	79
21783	79
21784	79
21797	79
21804	79
21805	79
21812	79
21905	79
21906	79
21907	79
21919	79
21923	79
21924	79
21935	79
21942	79
22007	79
22011	79
22016	79
22021	79
22037	79
22045	79
22046	79
22051	79
22056	79
22057	79
22067	79
22073	79
22091	79
22092	79
22094	79
22096	79
22101	79
22115	79
22122	79
22133	79
22135	79
22140	79
22159	79
22166	79
22183	79
22188	79
22189	79
22191	79
22192	79
22211	79
22218	79
22225	79
22228	79
22240	79
22241	79
22242	79
22243	79
22244	79
22253	79
22254	79
22255	79
22263	79
22274	79
22277	79
22290	79
22300	79
22326	79
22327	79
22328	79
22334	79
22335	79
22339	79
22342	79
22343	79
22350	79
22351	79
22360	79
22368	79
22374	79
22549	79
22716	79
22717	79
22721	79
22725	79
22726	79
22727	79
22733	79
22736	79
22741	79
22743	79
22749	79
22754	79
22759	79
22760	79
22761	79
22762	79
22763	79
22764	79
22766	79
22773	79
22774	79
22774	79
22777	79
22783	79
22786	79
22793	79
22864	79
22871	79
22872	79
22874	79
22885	79
22888	79
22896	79
22897	79
22902	79
22967	79
22968	79
22970	79
22984	79
22985	79
22992	79
22994	79
22997	79
23002	79
23017	79
23018	79
23020	79
23023	79
23025	79
23029	79
23031	79
23037	79
23038	79
23043	79
23045	79
23053	79
23054	79
23064	79
23065	79
23072	79
23073	79
23088	79
23089	79
23090	79
23108	79
23125	79
23131	79
23140	79
23143	79
23144	79
23148	79
23154	79
23158	79
23167	79
23169	79
23173	79
23178	79
23179	79
23180	79
23181	79
23187	79
23188	79
23191	79
23193	79
23199	79
23200	79
23202	79
23203	79
23204	79
23240	79
23241	79
23242	79
23246	79
23248	79
23249	79
23250	79
23251	79
23259	79
23261	79
23263	79
23264	79
23265	79
23266	79
23273	79
23274	79
23285	79
23292	79
23294	79
23298	79
23299	79
23300	79
23302	79
23334	79
23335	79
23339	79
23341	79
23352	79
23353	79
23367	79
23370	79
23371	79
23372	79
23377	79
23379	79
23381	79
23387	79
23390	79
23402	79
23403	79
23404	79
23406	79
23448	79
23451	79
23460	79
23462	79
23466	79
23467	79
23475	79
23477	79
23478	79
23480	79
23489	79
23490	79
23491	79
23497	79
23504	79
23508	79
23512	79
23514	79
23556	79
23561	79
23579	79
23584	79
23585	79
23588	79
23666	79
23667	79
23671	79
23672	79
23675	79
23687	79
23691	79
23692	79
23696	79
23706	79
23707	79
23710	79
23711	79
23715	79
23717	79
23726	79
23732	79
23733	79
23736	79
23741	79
23782	79
23785	79
23786	79
23791	79
23792	79
23793	79
23804	79
23821	79
23832	79
23833	79
23834	79
23844	79
23849	79
23850	79
23853	79
23856	79
23860	79
23869	79
23875	79
23887	79
23889	79
23894	79
23896	79
24224	79
24236	79
24237	79
24259	79
24260	79
24285	79
24290	79
24291	79
24311	79
24312	79
24319	79
24321	79
24327	79
24333	79
24335	79
24337	79
24352	79
24388	79
24390	79
24393	79
24400	79
24410	79
24420	79
24421	79
24425	79
24429	79
24434	79
24438	79
24439	79
24441	79
24448	79
24450	79
24451	79
24452	79
24490	79
24497	79
24500	79
24507	79
24509	79
24510	79
24521	79
24530	79
24617	79
24623	79
24632	79
24634	79
24645	79
24651	79
24652	79
24653	79
24657	79
24658	79
24667	79
24672	79
24676	79
24687	79
24727	79
24734	79
24737	79
24740	79
24741	79
24743	79
24751	79
24752	79
24758	79
24762	79
24764	79
24765	79
24766	79
24767	79
24779	79
24780	79
24785	79
24787	79
24788	79
24791	79
24797	79
24798	79
24800	79
24801	79
24814	79
24818	79
24819	79
24820	79
24821	79
24826	79
24830	79
24927	79
24929	79
24931	79
24937	79
24938	79
24943	79
24945	79
24949	79
24953	79
24953	79
24961	79
24962	79
24964	79
24965	79
24966	79
24969	79
24978	79
24979	79
24981	79
24991	79
25000	79
25003	79
25004	79
25006	79
25038	79
25041	79
25045	79
25063	79
25065	79
25067	79
25069	79
25077	79
25079	79
25087	79
25089	79
25096	79
25149	79
25153	79
25158	79
25159	79
25160	79
25170	79
25175	79
25175	79
25178	79
25179	79
25180	79
25183	79
25189	79
25192	79
25193	79
25200	79
25207	79
25253	79
25254	79
25259	79
25260	79
25274	79
25275	79
25276	79
25281	79
25285	79
25291	79
25334	79
25336	79
25342	79
25344	79
25346	79
25353	79
25355	79
25356	79
25364	79
25368	79
25370	79
25379	79
25380	79
25383	79
25387	79
25392	79
25395	79
25405	79
25412	79
25413	79
25420	79
25424	79
25428	79
25435	79
25438	79
25624	79
25763	79
25764	79
25766	79
25767	79
25843	79
25846	79
25847	79
25851	79
25852	79
25853	79
25855	79
25865	79
25869	79
25871	79
25872	79
25874	79
25876	79
25877	79
25886	79
25887	79
25888	79
25893	79
25898	79
25900	79
25902	79
25903	79
25904	79
25914	79
25915	79
25929	79
25930	79
25931	79
25950	79
25962	79
25963	79
25970	79
25975	79
25980	79
25982	79
25986	79
25993	79
25998	79
26006	79
26010	79
26018	79
26041	79
26043	79
26047	79
26050	79
26052	79
26053	79
26054	79
26055	79
26062	79
26063	79
26064	79
26068	79
26075	79
26078	79
26079	79
26080	79
26081	79
26086	79
26088	79
26092	79
26101	79
26103	79
26110	79
26112	79
26116	79
26125	79
26127	79
26127	79
26168	79
26181	79
26184	79
26197	79
26236	79
26240	79
26244	79
26249	79
26256	79
26259	79
26260	79
26262	79
26266	79
26272	79
26273	79
26274	79
26275	79
26277	79
26278	79
26282	79
26284	79
26285	79
26292	79
26293	79
26295	79
26296	79
26298	79
26299	79
26304	79
26308	79
26311	79
26312	79
26317	79
26322	79
26324	79
26325	79
26330	79
26331	79
26337	79
26339	79
26340	79
26341	79
26343	79
26347	79
26349	79
26448	79
26451	79
26454	79
26455	79
26459	79
26462	79
26463	79
26464	79
26485	79
26491	79
26493	79
26497	79
26497	79
26498	79
26506	79
26507	79
26508	79
26509	79
26513	79
26520	79
26521	79
26522	79
26528	79
26535	79
26537	79
26542	79
26544	79
26550	79
26552	79
26553	79
26573	79
26581	79
26584	79
26586	79
26595	79
26597	79
26598	79
26606	79
26614	79
26619	79
26622	79
26623	79
26624	79
26628	79
26632	79
26638	79
26644	79
26679	79
26680	79
26687	79
26688	79
26694	79
26696	79
26702	79
26714	79
26720	79
26725	79
26726	79
26729	79
26733	79
26734	79
26735	79
26737	79
26778	79
26781	79
26783	79
26787	79
26798	79
26808	79
26814	79
27011	79
27015	79
27016	79
27017	79
27030	79
27033	79
27034	79
27036	79
27041	79
27045	79
27048	79
27052	79
27057	79
27058	79
27063	79
27064	79
27070	79
27072	79
27074	79
27083	79
27086	79
27089	79
27091	79
27096	79
27097	79
27098	79
27103	79
27113	79
27116	79
27117	79
27197	79
27198	79
27199	79
27201	79
27202	79
27207	79
27213	79
27217	79
27221	79
27223	79
27228	79
27232	79
27234	79
27235	79
27236	79
27240	79
27248	79
27250	79
27254	79
27256	79
27261	79
27262	79
27264	79
27269	79
27271	79
27279	79
27284	79
27290	79
27291	79
27295	79
27374	79
27375	79
27376	79
27388	79
27390	79
27391	79
27396	79
27416	79
27430	79
27440	79
27444	79
27450	79
27456	79
27461	79
27466	79
27474	79
27475	79
27509	79
27523	79
27524	79
27525	79
27531	79
27532	79
27542	79
27548	79
27549	79
27558	79
27559	79
27567	79
27570	79
27577	79
27604	79
27605	79
27608	79
27614	79
27617	79
27618	79
27622	79
27629	79
27635	79
27636	79
27638	79
27639	79
27644	79
27648	79
27649	79
27659	79
27668	79
27671	79
27673	79
27677	79
27679	79
27684	79
27729	79
27730	79
27732	79
27735	79
27740	79
27741	79
27747	79
27759	79
27911	79
27912	79
27919	79
27922	79
28002	79
28003	79
28004	79
28015	79
28017	79
28019	79
28020	79
28028	79
28040	79
28041	79
28052	79
28055	79
28056	79
28057	79
28075	79
28082	79
28085	79
28093	79
28095	79
28122	79
28142	79
28143	79
28144	79
28148	79
28171	79
28180	79
28191	79
28192	79
28226	79
28227	79
28228	79
28233	79
28240	79
28241	79
28255	79
28257	79
28262	79
28263	79
28264	79
28270	79
28274	79
28276	79
28277	79
28278	79
28279	79
28286	79
28286	79
28291	79
28300	79
28330	79
28334	79
28336	79
28339	79
28343	79
28347	79
28361	79
28572	79
28573	79
28576	79
28577	79
28578	79
28583	79
28599	79
28607	79
28609	79
28613	79
28614	79
28617	79
28618	79
28625	79
28628	79
28633	79
28644	79
28655	79
28661	79
28665	79
28666	79
28670	79
28671	79
28677	79
28678	79
28681	79
28768	79
28769	79
28777	79
28780	79
28786	79
28787	79
28791	79
28792	79
28793	79
28801	79
28803	79
28808	79
28814	79
28820	79
28821	79
28822	79
28823	79
28824	79
28825	79
28830	79
28833	79
28838	79
28845	79
28849	79
28851	79
28853	79
28856	79
28861	79
28862	79
28869	79
28870	79
28871	79
28874	79
28969	79
28971	79
28972	79
28980	79
28984	79
28995	79
29000	79
29002	79
29014	79
29015	79
29016	79
29023	79
29031	79
29034	79
29036	79
29039	79
29046	79
29073	79
29074	79
29125	79
29134	79
29140	79
29144	79
29145	79
29146	79
29147	79
29155	79
29158	79
29160	79
29167	79
29169	79
29171	79
29173	79
29174	79
29175	79
29202	79
29204	79
29207	79
29208	79
29210	79
29211	79
29212	79
29217	79
29223	79
29225	79
29226	79
29233	79
29241	79
29242	79
29248	79
29249	79
29250	79
29254	79
29258	79
29268	79
29270	79
29276	79
29280	79
29284	79
29324	79
29325	79
29327	79
29334	79
29335	79
29348	79
29353	79
29354	79
------------

2336 rows

Open in new window

0
 

Author Comment

by:John500
ID: 33554120
I managed to drop the wrong results in that last one - duplicates.  I'll post the correct code when I return - headed out for a minute.
0
 

Author Comment

by:John500
ID: 33554382
Thanks for the 'angle' on this.  I left out the DISTINCT on that last post:

declare @c table (CustomerID int)
;
insert @c select DISTINCT c.CustomerID
FROM sales.customer c
INNER JOIN sales.CustomerAddress ca ON c.CustomerID=ca.CustomerID
INNER JOIN person.address pa ON ca.addressID = pa.addressid
WHERE pa.StateProvinceID = 79
;

As a last example of many angles and input for collaboration, one of these questioins talked about updating from the sales price to the recommended sales price.  There is no recommended sales price in the database.  An angle from another person might be a suggestion like 'list' price.... which in that case I agree with completely....

One fact that is blatantly obvious in today's fast paced world is that tests are given at home and for use with on-line resources.  Therefore, such sites like this should keep the main thing the main thing.  The main thing is to collaborate.
0
 

Author Comment

by:John500
ID: 33554396
cyberkiwi,

Any input on that cascade issue.  I can't delete due to restraints.  I could drop restraints first I suppose but I'm thinking cascade takes care of that, yes/no?

What's your angle on this?
0
 

Author Comment

by:John500
ID: 33554416
... I noticed you used:

where a.StateProvince = 'Maine'

That's an interesting angle of this public database used for learning throughout are fine world.  Can you tell me why you chose to use that clause?

I was sticking with the state of WA as the sole qualifier since this poorly worded question includes the word state?  Is there something about this database I should be familar with ?

Thanks
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33554919
>> I could drop restraints first I suppose but I'm thinking cascade takes care of that, yes/no?

Cascaded deletes happen only if you set them up as an option of the constraint.  This is useful if you have information that you don't care about once you remove the primary - this could be customers notes.  Once you delete a customer, you don't care about notes.

On the other hand, a customer foreign key constraint on invoice will probably never be set up as "on cascade delete".  If you removed the customer accidentally, there goes all the unpaid invoices linked to the customer.

To handle a constrained delete involving foreign keys, you have to manage the deletion in stages.  One option is the type of code shown in http:#a33552257 above.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 33561512
It is always very difficult to do cascading deletes unless the Foreign Key has been set up with ON DELETE CASCADE options.

Adventureworks has NOT been set up with those options in the foreign keys, so, have to go find them first...

Here is a bit of T-SQL that helps with finding some of those constraints on CustomerID, or more accurately on Customer Table :

select k.table_name as 'Master Table', k.column_name as 'Master Column', k.constraint_name as 'Master Key',
r.constraint_name as 'Detail Constraint',
c.table_name as 'Detail Table', c.column_name as 'Detail Column'
from information_schema.referential_constraints r
inner join information_schema.key_column_usage k on r.unique_constraint_name = k.constraint_name
inner join information_schema.key_column_usage c on r.constraint_name = c.constraint_name
inner join information_schema.table_constraints t on r.constraint_name = t.constraint_name and c.table_name = t.table_name
WHERE t.constraint_type = 'foreign key'
and k.table_name like 'customer%'
go


As you can see you need to first drop those entries in the detail table first - but they can also have other foreign keys on them. So, need to track through those tables as well...

Then, simply delete from the bottom up, table by table for the key values that you are interested in...

0
 

Author Comment

by:John500
ID: 34131341
Mark_wills,  

Can you tell me how the code in your last post should be run?  If I run this under the AdventureWorks database I get the error:

Invalid object name 'information_schema.referential_constraints'

Thanks
0
 

Author Comment

by:John500
ID: 34131498
if I run the code using the AdventureWorks database and add the master database prefix like this:

FROM master.information_schema.referential_constraints r

... then I get no errors but no results are returned except the column headers, i.e:

Master Table  |  Master Column  |  Master Key  |  Detail Constraint  |  Detail Table  |  Detail Column
------------------------------------------------------------------------------------------------------------------------
0
 

Author Comment

by:John500
ID: 34131991
After considering the options here, I decided it would be easier to use the following command on tables required:

ALTER TABLE Sales.CustomerAddress NOCHECK CONSTRAINT ALL

This command toggles the 'Enforce Foreign Key Constraint' property rather than dropping the constraints.  Conversely, the following command puts the constraint back:

ALTER TABLE Sales.CustomerAddress CHECK CONSTRAINT ALL

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34132619
The information_schema.referential_constraints should be there. What version of adventureworks are you using ? Certainly able to use it in my version - downloaded for SQL 2005

See the output below...

Customer	CustomerID	PK_Customer_CustomerID	FK_CustomerAddress_Customer_CustomerID	CustomerAddress	CustomerID
Customer	CustomerID	PK_Customer_CustomerID	FK_Individual_Customer_CustomerID		Individual		CustomerID
Customer	CustomerID	PK_Customer_CustomerID	FK_SalesOrderHeader_Customer_CustomerID	SalesOrderHeader	CustomerID
Customer	CustomerID	PK_Customer_CustomerID	FK_Store_Customer_CustomerID		Store		CustomerID

Open in new window

0
 

Author Comment

by:John500
ID: 34134921
Mark wills,

I reinstalled and that cleared up whatever problem I was having.  I get the same results.

Thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question