Help with query against AdventureWorks database - Delete Customers
Need a hand with this query:
Delete the customer(s) from the database who are from the state of Maine ('WA').
Thanks!
Delete the customer(s) from the database who are from the state of Maine ('WA').
Thanks!
Minor correction:
delete c
from customer c
inner join CustomerAddress ca on c.CustomerID=ca.CustomerID
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'
);
delete c
from customer c
inner join CustomerAddress ca on c.CustomerID=ca.CustomerID
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'
);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
... 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
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 rowsASKER
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.
ASKER
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.
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.
ASKER
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?
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?
ASKER
... 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
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
>> 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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.refere
Thanks
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.refere
Thanks
ASKER
if I run the code using the AdventureWorks database and add the master database prefix like this:
FROM master.information_schema.
... 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
--------------------------
FROM master.information_schema.
... 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
--------------------------
ASKER
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
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
The information_schema.referen
See the output below...
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 CustomerIDASKER
Mark wills,
I reinstalled and that cleared up whatever problem I was having. I get the same results.
Thanks
I reinstalled and that cleared up whatever problem I was having. I get the same results.
Thanks
from customer c
inner join CustomerAddress ca on c.CustomerID=ca.CustomerID
inner join Address a on a.AddressID=ca.AddressID
where a.StateProvince = 'Maine'