Update Statement with an outer apply

  • 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.

  • 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

  • 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'.

  • 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

  • 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

  • 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'

  • 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.

  • 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)

  • Nope, that only updates the single record that already has the cxa_on_hold_flag

  • 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?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • 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

  • Stephen, what do you get when you run this:

    SELECT *

    FROM co_customer_x_address

    where cxa_cst_key = '18599564-1e26-4f0d-b95b-000d09d32406'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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