Solved

Help with query against AdventureWorks database - Delete Customers

Posted on 2010-08-28
17
777 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now