Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Update Statement with an outer apply Expand / Collapse
Author
Message
Posted Tuesday, April 7, 2009 11:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:19 AM
Points: 75, Visits: 231
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.
Post #692351
Posted Tuesday, April 7, 2009 11:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
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


Ken Simmons
http://twitter.com/KenSimmons
Post #692362
Posted Tuesday, April 7, 2009 11:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:19 AM
Points: 75, Visits: 231
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'.
Post #692365
Posted Tuesday, April 7, 2009 11:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
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


Ken Simmons
http://twitter.com/KenSimmons
Post #692367
Posted Tuesday, April 7, 2009 11:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:19 AM
Points: 75, Visits: 231
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
Post #692380
Posted Tuesday, April 7, 2009 12:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
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'


Ken Simmons
http://twitter.com/KenSimmons
Post #692404
Posted Tuesday, April 7, 2009 12:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:19 AM
Points: 75, Visits: 231
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.
Post #692406
Posted Tuesday, April 7, 2009 12:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
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)


Ken Simmons
http://twitter.com/KenSimmons
Post #692410
Posted Tuesday, April 7, 2009 12:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:19 AM
Points: 75, Visits: 231
Nope, that only updates the single record that already has the cxa_on_hold_flag
Post #692412
Posted Tuesday, April 7, 2009 12:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
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
Exploring Recursive CTEs by Example Dwain Camps
Post #692426
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse