April 7, 2009 at 1:59 pm
I think we were also trying to run the update based on the wrong key.
update co_customer_x_address
set cxa_on_hold_flag = '1'
Where cxa_cst_key IN (Select cxa_cst_key FROM co_customer_x_address WHERE cxa_on_hold_flag = '1')
April 7, 2009 at 2:07 pm
That seemed to work, I will have to go through some more testing to make sure, but seem things look good at this point.
April 7, 2009 at 2:15 pm
Stephen crocker (4/7/2009)
From my first post when I asked the question the Select statement works after the few minor fixes, I get the exact results I was expecting. However when I run the update statement it will update everyone and set the cxa_on_hold_flag to 0 not what I expected or asked the query when I ran it. I just wanted it to update the users that were associated with the Guid in the queryupdate co_customer_x_address
set cxa_on_hold_flag = '1'
from
(select distinct a1.cxa_mailing_label, a1.cxa_adr_key, M.cxa_adr_key
from co_customer_x_address a1
outer apply
(select a2.cxa_adr_key from co_customer_x_address a2
where a1.cxa_adr_key = a2.cxa_adr_key) M
Where a1.cxa_adr_key = '4490a7ff-cbfe-4386-8643-ec8cec854410')
The area that is in Bold gives me exactly what I wanted so the update is not picking this up and updateing everyone
The problem is that the update doesn't actually have a Where clause. It just has a From clause. Without the Where clause, connecting the update table to the derived table in the From clause, it will update all rows.
Change your From clause to "From (select 1 as Col)X", and it'll still update all rows, for the same reason. Nothing tells it to limit the Update.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply