April 7, 2009 at 11:28 am
I have the following statement, It will not run giving me the following message
Msg 102 Level 15 State 1 Line 10
Incorrect syntax near ')'.
here is the code
update 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')
If I run the Middle Select statement by it self it runs fine and returns the exact expected results but when I try to add the upate statemetn it doesn't work.
Anyone know what I am doing wrong and can guide or help be figure this out.
April 7, 2009 at 11:35 am
The first thing that stands out to me is that you need to alias the query in the from clause.
update 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') A
April 7, 2009 at 11:37 am
Ok, did that and it took the initial message out now I have this error
Msg 8156, Level 16, State 1, Line 1
The column 'cxa_adr_key' was specified multiple times for 'A'.
April 7, 2009 at 11:41 am
You have it in the query twice with the same name.
update 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') A
April 7, 2009 at 11:53 am
Well, that did work however it updated every row in the table to set the cxa_on_hold_flag to 1 and not just the subset of data in the select statement
April 7, 2009 at 12:15 pm
After looking at the query a little more, I am not quire sure what you are trying to do. It looks to me like you are just trying to update the records for a given address key. In that case something like this would work. Can you give a little background on what you are trying to do?
update co_customer_x_address
set cxa_on_hold_flag = '1'
Where cxa_adr_key = '4490a7ff-cbfe-4386-8643-ec8cec854410'
April 7, 2009 at 12:20 pm
It's a little more complex then that, what I need to do is find an organization that has the cxa_on_hold_flag set to 1 and find anyone who has the same cxa_adr_key and set their cxa_on_hold_flag's to 1 as well. The only reason I have a Key in the query right now is to find specific company and check that. Does this make since?
so basically
If organization cxa_on_hold_flag = 1
Then update everyone that has the cxa_adr_key the same as the organization update with cxa_on_hold flag to 1.
Hope this makes since.
April 7, 2009 at 12:28 pm
If I understand correctly, you should be able to use this. The in clause will return all of the cxa_adr_key values that currently have cxa_on_hold_flag = 1. The update statement will then update all of the cxa_on_hold_flag = 1 for those keys. Does this look like what you want?
update co_customer_x_address
set cxa_on_hold_flag = '1'
Where cxa_adr_key IN (Select cxa_adr_key WHERE cxa_on_hold_flag = 1)
April 7, 2009 at 12:34 pm
Nope, that only updates the single record that already has the cxa_on_hold_flag
April 7, 2009 at 12:55 pm
Stephen crocker (4/7/2009)
Nope, that only updates the single record that already has the cxa_on_hold_flag
What value does it have for cxa_adr_key?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 1:26 pm
Can you provide some sample data? First show how it currently looks and then show how you would like it to look after the update.
April 7, 2009 at 1:47 pm
Ok here is the organizaiton record
SELECT cxa_key, cxa_cst_key , cxa_adr_key, cxa_adt_key, cxa_mail_stop, cxa_on_hold_flag, cxa_on_hold_from, cxa_on_hold_through, cxa_adh_key,
cxa_seasonal_from_date, cxa_seasonal_through_date, cxa_add_date, cxa_change_date, cxa_change_user, cxa_delete_flag, cxa_entity_key
FROM co_customer_x_address
where cxa_cst_key = '18599564-1e26-4f0d-b95b-000d09d32406' and cxa_on_hold_flag = '1'
Results
59730A9F-A807-4D5C-915E-EFC186DAC51D18599564-1E26-4F0D-B95B-000D09D324064490A7FF-CBFE-4386-8643-EC8CEC85441034A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL12009-03-05 00:00:00NULL933B7D6A-7257-4135-86B1-4AFEA8803A1FNULLNULL2007-08-05 13:50:00beckyd0NULL
here are the people associated with that company ( it's a subset) As you can see field
--------------------Results-------------------------------------------
9596BE32-ABF3-4907-AFE6-00084AD9BA1C18599564-1E26-4F0D-B95B-000D09D3240603D792AA-50ED-4BFD-A5A8-F8F8EC87302F837DA3B4-F637-4637-9DDD-362F0E1125C3NULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
391452CC-EC25-455D-B3F9-268A727E980118599564-1E26-4F0D-B95B-000D09D324069BECC3D7-1EDA-406B-BBCC-71DA5FCE5432B57B7C69-5191-4767-98DE-6FD4995CB12CNULL0NULLNULLNULLNULLNULL2007-09-07 16:35:00NULLNULL0NULL
287AF93A-B965-435A-AE2F-2B0AF98441FB18599564-1E26-4F0D-B95B-000D09D32406DA6FA31C-C6C4-483C-9A94-0A3FA357C718B57B7C69-5191-4767-98DE-6FD4995CB12CNULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
53F849F3-E31A-4C2A-A9D7-68F10A1FC2F518599564-1E26-4F0D-B95B-000D09D32406B88C00FB-CACD-4842-A027-F2A2357BB61F34A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
BEA08903-4009-469D-A884-6D378C9099B418599564-1E26-4F0D-B95B-000D09D32406221C8048-306D-48DA-9D71-0CB1598CE67334A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
92499C67-FF30-4640-9E28-98112D347A1918599564-1E26-4F0D-B95B-000D09D324067C139F15-1D73-45D4-B898-53BD4273D47A34A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
86CFD8DC-8BD2-4DEC-AE61-A3C5D2321C4318599564-1E26-4F0D-B95B-000D09D32406F1BF3407-8B9A-4C3C-8183-78DAAE9B5BE03F649A0E-434D-4547-817A-C19935E3BFBANULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
78A7E8E7-8CB6-4494-A931-C1074A56308918599564-1E26-4F0D-B95B-000D09D32406230068C7-2E14-45E1-98E8-1BC32311BCC834A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
49F647CF-A980-4DF6-9FEE-E13D46D6FA6D18599564-1E26-4F0D-B95B-000D09D32406E060AA63-9D37-4319-9552-F67B84C605BF34A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
3B01ED46-0CC5-4BA2-B961-E56240C9AD4418599564-1E26-4F0D-B95B-000D09D3240694F56810-7A8D-422B-B258-12FD42A55ADA05FD82A8-2718-4D21-9D5E-AE043DA647DCNULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
59730A9F-A807-4D5C-915E-EFC186DAC51D18599564-1E26-4F0D-B95B-000D09D324064490A7FF-CBFE-4386-8643-EC8CEC85441034A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL12009-03-05 00:00:00NULL933B7D6A-7257-4135-86B1-4AFEA8803A1FNULLNULL2007-08-05 13:50:002009-03-10 11:31:00beckyd0NULL
EF6B88EE-0824-4D5C-AD69-F2E7465E9E5B18599564-1E26-4F0D-B95B-000D09D324068A6C9A07-DEB2-4BF6-B117-C9CE0ACA716734A78CD2-9D2A-45CC-8FEA-98DE23EF52D4NULL0NULLNULLNULLNULLNULL2007-08-05 13:50:002007-10-25 19:58:00netforumituser0NULL
As you can see if you take this to excel or I can email you a spreadsheet with the data better formatted. That the cxa_on_hold_flag is set to 0 except for one record (that is the organization record) I need to be able to run something to get the results (not for a specific key but everyone) and if the organization key is on hold then all of the child addresses will be on hold as well. (Referencing the cxa_cst_key)
If it's easier I can email you the Excel files with sample data
April 7, 2009 at 1:49 pm
Stephen, what do you get when you run this:
SELECT *
FROM co_customer_x_address
where cxa_cst_key = '18599564-1e26-4f0d-b95b-000d09d32406'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 1:54 pm
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 query
update 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
April 7, 2009 at 1:56 pm
This should do what you're asking for. It's very similar to what was already suggested, but has a From clause missed in the prior post that might make a difference.
UPDATE
co_customer_x_address
SET cxa_on_hold_flag = '1'
WHERE
cxa_adr_key IN (SELECT
cxa_adr_key
FROM
co_customer_x_address a2
WHERE
cxa_on_hold_flag = '1');
Try it out, let me know if it still doesn't do what you need. If not, then there's a problem with the specification.
- 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 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply