Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ANSI SQL multicolumn update with subquery

12 posts in ,  Performance and Tuning General Discussion Last posting was on 2007-11-13 17:40:38.0Z
Gareth Davies Posted on 2007-11-02 13:36:42.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: ANSI SQL multicolumn update with subquery
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <472b27ea$1@forums-1-dub>
Date: 2 Nov 2007 05:36:42 -0800
X-Trace: forums-1-dub 1194010602 87.194.125.184 (2 Nov 2007 05:36:42 -0800)
X-Original-Trace: 2 Nov 2007 05:36:42 -0800, 87-194-125-184.bethere.co.uk
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23492 sybase.public.ase.performance+tuning:10426
Article PK: 71425

I'm trying to update two columns of a table using a sub-query involving
the table itself. I'm getting "ambiguous table" complaints using the
ANSI FROM form of UPDATE. The only way I can think to get this to work
in Sybase is by repeating the subquery in both SET statements.

Will the optimiser detect that it's the same query and run it once
instead of twice?

Alternatively, how do I write the ANSI equivalent of the following SQL?

UPDATE A
SET (A.col1, A.col2) =
( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
FROM A
LEFT OUTER JOIN B ON
( A.key = B.key
)
)
WHERE A.key IS NOT NULL

Cheers

Gareth


"Mark A. Parsons" <iron_horse Posted on 2007-11-02 23:46:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
References: <472b27ea$1@forums-1-dub>
In-Reply-To: <472b27ea$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
X-Original-NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
Message-ID: <472bb6f3$1@forums-1-dub>
Date: 2 Nov 2007 15:46:59 -0800
X-Trace: forums-1-dub 1194047219 67.87.252.233 (2 Nov 2007 15:46:59 -0800)
X-Original-Trace: 2 Nov 2007 15:46:59 -0800, ool-4357fce9.dyn.optonline.net
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23497 sybase.public.ase.performance+tuning:10427
Article PK: 71431

Yes, 2 subqueries will lead to 2 scans of the B table.

I'm not sure how you'd be able to 're-use' one of the sub-queries since each one will be running a different aggregate,
ie, the 2 sub-queries won't be the same.

---------------------

Since you're performing aggregates I'm not sure there is any 'straight forward' way to do this.

You've got the sub-query option, eg:

update A set
col1 = (select count(B.something) from B where B.key = A.key),
col2 = (select coalesce(sum(B.something),0) from B where B.key = A.key)

'course, you'll need to decide how you want to address those A.key records which don't have any matching B.key's.

One idea would be to wrap the sub-queries in coalesce/isnull logic (which will cause all records in A to be updated).

Another option would be to add a 'where exists()' sub-query to the top-level query (which should insure only A.key
records are updated that have a match in B).

----------

Then there's the view option, eg:

create view v2
as
select key,count(something) as count_b,sum(something) as sum_b
from B
group by key
go
-- which leads to something like ...

update A set
col1 = B.count_b,
col2 = B.sum_b
from B
where B.key = A.key
go

----------

And there's the #temp table option ... basically run the same query as the above view, putting the data into a #temp
table, then join A with the #temp table.

----------

... couldn't come up with a derived table version that would work if simply because ASE won't allow derived tables in an
UPDATE statement.

Gareth Davies wrote:
> I'm trying to update two columns of a table using a sub-query involving
> the table itself. I'm getting "ambiguous table" complaints using the
> ANSI FROM form of UPDATE. The only way I can think to get this to work
> in Sybase is by repeating the subquery in both SET statements.
>
> Will the optimiser detect that it's the same query and run it once
> instead of twice?
>
> Alternatively, how do I write the ANSI equivalent of the following SQL?
>
> UPDATE A
> SET (A.col1, A.col2) =
> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
> FROM A
> LEFT OUTER JOIN B ON
> ( A.key = B.key
> )
> )
> WHERE A.key IS NOT NULL
>
> Cheers
>
> Gareth


Gareth Davies Posted on 2007-11-05 11:08:03.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
References: <472b27ea$1@forums-1-dub> <472bb6f3$1@forums-1-dub>
In-Reply-To: <472bb6f3$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <472ef993$1@forums-1-dub>
Date: 5 Nov 2007 03:08:03 -0800
X-Trace: forums-1-dub 1194260883 87.194.125.184 (5 Nov 2007 03:08:03 -0800)
X-Original-Trace: 5 Nov 2007 03:08:03 -0800, 87-194-125-184.bethere.co.uk
Lines: 90
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23504 sybase.public.ase.performance+tuning:10428
Article PK: 71436

Thanks Mark

Those are pretty much the options I'd considered. It does seem a shame
that ANSI SQL doesn't support this syntax. I think I opt for the temp
table approach as it'll be easy to maintain.

Mark A. Parsons wrote:
> Yes, 2 subqueries will lead to 2 scans of the B table.
>
> I'm not sure how you'd be able to 're-use' one of the sub-queries since
> each one will be running a different aggregate, ie, the 2 sub-queries
> won't be the same.
>
> ---------------------
>
> Since you're performing aggregates I'm not sure there is any 'straight
> forward' way to do this.
>
> You've got the sub-query option, eg:
>
> update A set
> col1 = (select count(B.something) from B where B.key = A.key),
> col2 = (select coalesce(sum(B.something),0) from B where B.key = A.key)
>
> 'course, you'll need to decide how you want to address those A.key
> records which don't have any matching B.key's.
>
> One idea would be to wrap the sub-queries in coalesce/isnull logic
> (which will cause all records in A to be updated).
>
> Another option would be to add a 'where exists()' sub-query to the
> top-level query (which should insure only A.key records are updated that
> have a match in B).
>
> ----------
>
> Then there's the view option, eg:
>
> create view v2
> as
> select key,count(something) as count_b,sum(something) as sum_b
> from B
> group by key
> go
> -- which leads to something like ...
>
> update A set
> col1 = B.count_b,
> col2 = B.sum_b
> from B
> where B.key = A.key
> go
>
> ----------
>
> And there's the #temp table option ... basically run the same query as
> the above view, putting the data into a #temp table, then join A with
> the #temp table.
>
> ----------
>
> ... couldn't come up with a derived table version that would work if
> simply because ASE won't allow derived tables in an UPDATE statement.
>
>
> Gareth Davies wrote:
>> I'm trying to update two columns of a table using a sub-query
>> involving the table itself. I'm getting "ambiguous table" complaints
>> using the ANSI FROM form of UPDATE. The only way I can think to get
>> this to work in Sybase is by repeating the subquery in both SET
>> statements.
>>
>> Will the optimiser detect that it's the same query and run it once
>> instead of twice?
>>
>> Alternatively, how do I write the ANSI equivalent of the following SQL?
>>
>> UPDATE A
>> SET (A.col1, A.col2) =
>> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
>> FROM A
>> LEFT OUTER JOIN B ON
>> ( A.key = B.key
>> )
>> )
>> WHERE A.key IS NOT NULL
>>
>> Cheers
>>
>> Gareth


Prasan Medapati Posted on 2007-11-05 11:55:31.0Z
Sender: c79.472f0285.1804289383@sybase.com
From: Prasan Medapati
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <472f04b3.ca0.1681692777@sybase.com>
References: <472ef993$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Nov 2007 03:55:31 -0800
X-Trace: forums-1-dub 1194263731 10.22.241.41 (5 Nov 2007 03:55:31 -0800)
X-Original-Trace: 5 Nov 2007 03:55:31 -0800, 10.22.241.41
Lines: 107
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10429
Article PK: 89061

Gareth,

You can try it out this way:

UPDATE A
SET (col1, col2) =
( SELECT COUNT(B.something)
,COALESCE(SUM(B.something),0)
FROM A x
LEFT OUTER JOIN B ON ( x.key = B.key )
WHERE x.key IS NOT NULL
)

Cheers,
Prasan

> Thanks Mark
>
> Those are pretty much the options I'd considered. It does
> seem a shame that ANSI SQL doesn't support this syntax.
> I think I opt for the temp table approach as it'll be
> easy to maintain.
>
> Mark A. Parsons wrote:
> > Yes, 2 subqueries will lead to 2 scans of the B table.
> >
> > I'm not sure how you'd be able to 're-use' one of the
> > sub-queries since each one will be running a different
> > aggregate, ie, the 2 sub-queries won't be the same.
> >
> > ---------------------
> >
> > Since you're performing aggregates I'm not sure there is
> > any 'straight forward' way to do this.
> >
> > You've got the sub-query option, eg:
> >
> > update A set
> > col1 = (select count(B.something) from B where B.key =
> > A.key), col2 = (select coalesce(sum(B.something),0) from
> > B where B.key = A.key)
> > 'course, you'll need to decide how you want to address
> > those A.key records which don't have any matching
> > B.key's.
> > One idea would be to wrap the sub-queries in
> > coalesce/isnull logic (which will cause all records in
> > A to be updated).
> > Another option would be to add a 'where exists()'
> > sub-query to the top-level query (which should insure
> > only A.key records are updated that have a match in B).
> >
> > ----------
> >
> > Then there's the view option, eg:
> >
> > create view v2
> > as
> > select key,count(something) as count_b,sum(something) as
> > sum_b from B
> > group by key
> > go
> > -- which leads to something like ...
> >
> > update A set
> > col1 = B.count_b,
> > col2 = B.sum_b
> > from B
> > where B.key = A.key
> > go
> >
> > ----------
> >
> > And there's the #temp table option ... basically run the
> > same query as the above view, putting the data into a
> > #temp table, then join A with the #temp table.
> >
> > ----------
> >
> > ... couldn't come up with a derived table version that
> > would work if simply because ASE won't allow derived
> > tables in an UPDATE statement.
> >
> > Gareth Davies wrote:
> >> I'm trying to update two columns of a table using a
> sub-query >> involving the table itself. I'm getting
> "ambiguous table" complaints >> using the ANSI FROM form
> of UPDATE. The only way I can think to get >> this to
> work in Sybase is by repeating the subquery in both SET
> >> statements. >>
> >> Will the optimiser detect that it's the same query and
> run it once >> instead of twice?
> >>
> >> Alternatively, how do I write the ANSI equivalent of
> the following SQL? >>
> >> UPDATE A
> >> SET (A.col1, A.col2) =
> >> ( SELECT COUNT(B.something)
> ,COALESCE(SUM(B.something),0) >> FROM A
> >> LEFT OUTER JOIN B ON
> >> ( A.key = B.key
> >> )
> >> )
> >> WHERE A.key IS NOT NULL
> >>
> >> Cheers
> >>
> >> Gareth


Carl Kayser Posted on 2007-11-05 12:30:45.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.performance+tuning
References: <472ef993$1@forums-1-dub> <472f04b3.ca0.1681692777@sybase.com>
Subject: Re: ANSI SQL multicolumn update with subquery
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 146.142.34.192
X-Original-NNTP-Posting-Host: 146.142.34.192
Message-ID: <472f0cf5@forums-1-dub>
Date: 5 Nov 2007 04:30:45 -0800
X-Trace: forums-1-dub 1194265845 146.142.34.192 (5 Nov 2007 04:30:45 -0800)
X-Original-Trace: 5 Nov 2007 04:30:45 -0800, 146.142.34.192
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10430
Article PK: 89057


<Prasan Medapati> wrote in message
news:472f04b3.ca0.1681692777@sybase.com...
> Gareth,
>
> You can try it out this way:
>
> UPDATE A
> SET (col1, col2) =
> ( SELECT COUNT(B.something)
> ,COALESCE(SUM(B.something),0)
> FROM A x
> LEFT OUTER JOIN B ON ( x.key = B.key )
> WHERE x.key IS NOT NULL
> )
>
> Cheers,
> Prasan
>
>> Thanks Mark
>>

Uhhh, well, that is neither ANSI nor ASE SQL compliant. It appears to be an
Oracle-specific syntax.


"Paul Horan[TeamSybase]" Posted on 2007-11-13 17:40:38.0Z
From: "Paul Horan[TeamSybase]" <phoran AT sybase DOT com>
Newsgroups: sybase.public.ase.performance+tuning
References: <472ef993$1@forums-1-dub> <472f04b3.ca0.1681692777@sybase.com> <472f0cf5@forums-1-dub>
Subject: Re: ANSI SQL multicolumn update with subquery
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: horanpxp.sybase.com
X-Original-NNTP-Posting-Host: horanpxp.sybase.com
Message-ID: <4739e196$1@forums-1-dub>
Date: 13 Nov 2007 09:40:38 -0800
X-Trace: forums-1-dub 1194975638 10.24.50.249 (13 Nov 2007 09:40:38 -0800)
X-Original-Trace: 13 Nov 2007 09:40:38 -0800, horanpxp.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10461
Article PK: 89081


"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:472f0cf5@forums-1-dub...
>
> <Prasan Medapati> wrote in message
> news:472f04b3.ca0.1681692777@sybase.com...
>> Gareth,
>>
>> You can try it out this way:
>>
>> UPDATE A
>> SET (col1, col2) =
>> ( SELECT COUNT(B.something)
>> ,COALESCE(SUM(B.something),0)
>> FROM A x
>> LEFT OUTER JOIN B ON ( x.key = B.key )
>> WHERE x.key IS NOT NULL
>> )
>>
>> Cheers,
>> Prasan
>>
>>> Thanks Mark
>>>
>
> Uhhh, well, that is neither ANSI nor ASE SQL compliant. It appears to be
> an Oracle-specific syntax.

Nope - it's ANSI compliant syntax...
Check Glenn Paulley's response in this thread.

Paul Horan[TeamSybase]


Gareth Davies Posted on 2007-11-05 14:13:40.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
References: <472b27ea$1@forums-1-dub> <472bb6f3$1@forums-1-dub>
In-Reply-To: <472bb6f3$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <472f2514$4@forums-1-dub>
Date: 5 Nov 2007 06:13:40 -0800
X-Trace: forums-1-dub 1194272020 87.194.125.184 (5 Nov 2007 06:13:40 -0800)
X-Original-Trace: 5 Nov 2007 06:13:40 -0800, 87-194-125-184.bethere.co.uk
Lines: 102
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23506 sybase.public.ase.performance+tuning:10431
Article PK: 71438

I'm finding the UPDATE FROM syntax results in unexpected behaviour.
I've got the procedure working with a temp table but when I combine the
results using:
UPDATE #tmp_report
SET #tmp_report.Number_of_files = #tmp_file_stats.Number_of_files,
#tmp_report.Bytes_used = #tmp_file_stats.Bytes_used
FROM #tmp_report T1, #tmp_file_stats
WHERE T1.Site_ID = #tmp_file_stats.Site_ID
AND T1.Site_ID IS NOT NULL

although the results are correct, ASE reports that all of the rows were
updated in spite of the final statement IS NOT NULL. In other words, if
the table has 1000 rows of which 80 are not null, I get 1000 rows
reportedly updated. I don't use this reported number so it's ok, but
it's a bit disturbing.

G

Mark A. Parsons wrote:
> Yes, 2 subqueries will lead to 2 scans of the B table.
>
> I'm not sure how you'd be able to 're-use' one of the sub-queries since
> each one will be running a different aggregate, ie, the 2 sub-queries
> won't be the same.
>
> ---------------------
>
> Since you're performing aggregates I'm not sure there is any 'straight
> forward' way to do this.
>
> You've got the sub-query option, eg:
>
> update A set
> col1 = (select count(B.something) from B where B.key = A.key),
> col2 = (select coalesce(sum(B.something),0) from B where B.key = A.key)
>
> 'course, you'll need to decide how you want to address those A.key
> records which don't have any matching B.key's.
>
> One idea would be to wrap the sub-queries in coalesce/isnull logic
> (which will cause all records in A to be updated).
>
> Another option would be to add a 'where exists()' sub-query to the
> top-level query (which should insure only A.key records are updated that
> have a match in B).
>
> ----------
>
> Then there's the view option, eg:
>
> create view v2
> as
> select key,count(something) as count_b,sum(something) as sum_b
> from B
> group by key
> go
> -- which leads to something like ...
>
> update A set
> col1 = B.count_b,
> col2 = B.sum_b
> from B
> where B.key = A.key
> go
>
> ----------
>
> And there's the #temp table option ... basically run the same query as
> the above view, putting the data into a #temp table, then join A with
> the #temp table.
>
> ----------
>
> ... couldn't come up with a derived table version that would work if
> simply because ASE won't allow derived tables in an UPDATE statement.
>
>
> Gareth Davies wrote:
>> I'm trying to update two columns of a table using a sub-query
>> involving the table itself. I'm getting "ambiguous table" complaints
>> using the ANSI FROM form of UPDATE. The only way I can think to get
>> this to work in Sybase is by repeating the subquery in both SET
>> statements.
>>
>> Will the optimiser detect that it's the same query and run it once
>> instead of twice?
>>
>> Alternatively, how do I write the ANSI equivalent of the following SQL?
>>
>> UPDATE A
>> SET (A.col1, A.col2) =
>> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
>> FROM A
>> LEFT OUTER JOIN B ON
>> ( A.key = B.key
>> )
>> )
>> WHERE A.key IS NOT NULL
>>
>> Cheers
>>
>> Gareth


Gareth Davies Posted on 2007-11-05 14:32:57.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
References: <472b27ea$1@forums-1-dub> <472bb6f3$1@forums-1-dub> <472f2514$4@forums-1-dub>
In-Reply-To: <472f2514$4@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <472f2999$2@forums-1-dub>
Date: 5 Nov 2007 06:32:57 -0800
X-Trace: forums-1-dub 1194273177 87.194.125.184 (5 Nov 2007 06:32:57 -0800)
X-Original-Trace: 5 Nov 2007 06:32:57 -0800, 87-194-125-184.bethere.co.uk
Lines: 106
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23507 sybase.public.ase.performance+tuning:10432
Article PK: 71441

Agh! My bad. It's reporting the correct amount - I was pairing
statements with the wrong result sets in Sybase Workspace.

Gareth Davies wrote:
> I'm finding the UPDATE FROM syntax results in unexpected behaviour. I've
> got the procedure working with a temp table but when I combine the
> results using:
> UPDATE #tmp_report
> SET #tmp_report.Number_of_files = #tmp_file_stats.Number_of_files,
> #tmp_report.Bytes_used = #tmp_file_stats.Bytes_used
> FROM #tmp_report T1, #tmp_file_stats
> WHERE T1.Site_ID = #tmp_file_stats.Site_ID
> AND T1.Site_ID IS NOT NULL
>
> although the results are correct, ASE reports that all of the rows were
> updated in spite of the final statement IS NOT NULL. In other words, if
> the table has 1000 rows of which 80 are not null, I get 1000 rows
> reportedly updated. I don't use this reported number so it's ok, but
> it's a bit disturbing.
>
> G
>
> Mark A. Parsons wrote:
>> Yes, 2 subqueries will lead to 2 scans of the B table.
>>
>> I'm not sure how you'd be able to 're-use' one of the sub-queries
>> since each one will be running a different aggregate, ie, the 2
>> sub-queries won't be the same.
>>
>> ---------------------
>>
>> Since you're performing aggregates I'm not sure there is any 'straight
>> forward' way to do this.
>>
>> You've got the sub-query option, eg:
>>
>> update A set
>> col1 = (select count(B.something) from B where B.key = A.key),
>> col2 = (select coalesce(sum(B.something),0) from B where B.key = A.key)
>>
>> 'course, you'll need to decide how you want to address those A.key
>> records which don't have any matching B.key's.
>>
>> One idea would be to wrap the sub-queries in coalesce/isnull logic
>> (which will cause all records in A to be updated).
>>
>> Another option would be to add a 'where exists()' sub-query to the
>> top-level query (which should insure only A.key records are updated
>> that have a match in B).
>>
>> ----------
>>
>> Then there's the view option, eg:
>>
>> create view v2
>> as
>> select key,count(something) as count_b,sum(something) as sum_b
>> from B
>> group by key
>> go
>> -- which leads to something like ...
>>
>> update A set
>> col1 = B.count_b,
>> col2 = B.sum_b
>> from B
>> where B.key = A.key
>> go
>>
>> ----------
>>
>> And there's the #temp table option ... basically run the same query as
>> the above view, putting the data into a #temp table, then join A with
>> the #temp table.
>>
>> ----------
>>
>> ... couldn't come up with a derived table version that would work if
>> simply because ASE won't allow derived tables in an UPDATE statement.
>>
>>
>> Gareth Davies wrote:
>>> I'm trying to update two columns of a table using a sub-query
>>> involving the table itself. I'm getting "ambiguous table" complaints
>>> using the ANSI FROM form of UPDATE. The only way I can think to get
>>> this to work in Sybase is by repeating the subquery in both SET
>>> statements.
>>>
>>> Will the optimiser detect that it's the same query and run it once
>>> instead of twice?
>>>
>>> Alternatively, how do I write the ANSI equivalent of the following SQL?
>>>
>>> UPDATE A
>>> SET (A.col1, A.col2) =
>>> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
>>> FROM A
>>> LEFT OUTER JOIN B ON
>>> ( A.key = B.key
>>> )
>>> )
>>> WHERE A.key IS NOT NULL
>>>
>>> Cheers
>>>
>>> Gareth


jarl Posted on 2007-11-06 08:24:40.0Z
From: jarl@mimer.com
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
Date: Tue, 06 Nov 2007 00:24:40 -0800
Organization: http://groups.google.com
Lines: 19
Message-ID: <1194337480.984628.50870@z9g2000hsf.googlegroups.com>
References: <472b27ea$1@forums-1-dub>
NNTP-Posting-Host: 192.71.97.254
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1194337481 23298 127.0.0.1 (6 Nov 2007 08:24:41 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 6 Nov 2007 08:24:41 +0000 (UTC)
In-Reply-To: <472b27ea$1@forums-1-dub>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.9) Gecko/20071025 Firefox/2.0.0.9,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: z9g2000hsf.googlegroups.com; posting-host=192.71.97.254; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Path: forums-1-dub!forums-master!newswest.sybase.com!newsfeed2.dallas1.level3.net!news.level3.com!postnews.google.com!z9g2000hsf.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23523 sybase.public.ase.performance+tuning:10433
Article PK: 71459


On Nov 2, 2:36 pm, Gareth Davies <gr...@sitemaker.cc> wrote:
> Alternatively, how do I write theANSIequivalent of the followingSQL?
>
> UPDATE A
> SET (A.col1, A.col2) =
> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
> FROM A
> LEFT OUTER JOIN B ON
> ( A.key = B.key
> )
> )
> WHERE A.key IS NOT NULL

This is actually valid ANSI SQL syntax. Check out the non-core feature
T641, "Multiple column assignment".


/Jarl


Gareth Davies Posted on 2007-11-06 13:32:20.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
References: <472b27ea$1@forums-1-dub> <1194337480.984628.50870@z9g2000hsf.googlegroups.com>
In-Reply-To: <1194337480.984628.50870@z9g2000hsf.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <47306ce4$1@forums-1-dub>
Date: 6 Nov 2007 05:32:20 -0800
X-Trace: forums-1-dub 1194355940 87.194.125.184 (6 Nov 2007 05:32:20 -0800)
X-Original-Trace: 6 Nov 2007 05:32:20 -0800, 87-194-125-184.bethere.co.uk
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23528 sybase.public.ase.performance+tuning:10434
Article PK: 71462


jarl@mimer.com wrote:
> On Nov 2, 2:36 pm, Gareth Davies <gr...@sitemaker.cc> wrote:
>> Alternatively, how do I write theANSIequivalent of the followingSQL?
>>
>> UPDATE A
>> SET (A.col1, A.col2) =
>> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
>> FROM A
>> LEFT OUTER JOIN B ON
>> ( A.key = B.key
>> )
>> )
>> WHERE A.key IS NOT NULL
>
> This is actually valid ANSI SQL syntax. Check out the non-core feature
> T641, "Multiple column assignment".
>

Is it? Okay. It's not supported by ASE 12.5.2 though as far as I can
tell. Or is it?


Carl Kayser Posted on 2007-11-06 13:54:06.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
References: <472b27ea$1@forums-1-dub> <1194337480.984628.50870@z9g2000hsf.googlegroups.com>
Subject: Re: ANSI SQL multicolumn update with subquery
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: 146.142.34.192
X-Original-NNTP-Posting-Host: 146.142.34.192
Message-ID: <473071fe@forums-1-dub>
Date: 6 Nov 2007 05:54:06 -0800
X-Trace: forums-1-dub 1194357246 146.142.34.192 (6 Nov 2007 05:54:06 -0800)
X-Original-Trace: 6 Nov 2007 05:54:06 -0800, 146.142.34.192
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23529 sybase.public.ase.performance+tuning:10436
Article PK: 71463


<jarl@mimer.com> wrote in message
news:1194337480.984628.50870@z9g2000hsf.googlegroups.com...
> On Nov 2, 2:36 pm, Gareth Davies <gr...@sitemaker.cc> wrote:
>> Alternatively, how do I write theANSIequivalent of the followingSQL?
>>
>> UPDATE A
>> SET (A.col1, A.col2) =
>> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
>> FROM A
>> LEFT OUTER JOIN B ON
>> ( A.key = B.key
>> )
>> )
>> WHERE A.key IS NOT NULL
>
> This is actually valid ANSI SQL syntax. Check out the non-core feature
> T641, "Multiple column assignment".
>
>
> /Jarl
>

Which ANSI/ISO version are you referring to? I don't see it in SQL 2003.


Glenn Paulley Posted on 2007-11-06 14:58:33.0Z
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general,sybase.public.ase.performance+tuning
Subject: Re: ANSI SQL multicolumn update with subquery
References: <472b27ea$1@forums-1-dub> <1194337480.984628.50870@z9g2000hsf.googlegroups.com> <473071fe@forums-1-dub>
In-Reply-To: <473071fe@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: paulley-d620.sybase.com
X-Original-NNTP-Posting-Host: paulley-d620.sybase.com
Message-ID: <47308119$1@forums-1-dub>
Date: 6 Nov 2007 06:58:33 -0800
X-Trace: forums-1-dub 1194361113 10.25.99.239 (6 Nov 2007 06:58:33 -0800)
X-Original-Trace: 6 Nov 2007 06:58:33 -0800, paulley-d620.sybase.com
Lines: 62
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:23531 sybase.public.ase.performance+tuning:10437
Article PK: 71468

It's in SQL/2003 Part 2, Foundation, Section 14.12 (SET clause list):

<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]
<set clause> ::=
<multiple column assignment>
| <set target> <equals operator> <update source>
<set target> ::= <update target> | <mutated set clause>
<multiple column assignment> ::=
<set target list> <equals operator> <assigned row>
<set target list> ::=
<left paren> <set target> [ { <comma> <set target> }... ] <right
paren>
<assigned row> ::= <contextually typed row value expression>

As Jarl indicated, the <multiple column assignment> is not part of core
SQL/2003, but is optional package T641, which is tied to feature F641
(row value constructors). You can search through the standard to find
other usages of features T641 and F641.

Glenn

Carl Kayser wrote:
> <jarl@mimer.com> wrote in message
> news:1194337480.984628.50870@z9g2000hsf.googlegroups.com...
>> On Nov 2, 2:36 pm, Gareth Davies <gr...@sitemaker.cc> wrote:
>>> Alternatively, how do I write theANSIequivalent of the followingSQL?
>>>
>>> UPDATE A
>>> SET (A.col1, A.col2) =
>>> ( SELECT COUNT(B.something),COALESCE(SUM(B.something),0)
>>> FROM A
>>> LEFT OUTER JOIN B ON
>>> ( A.key = B.key
>>> )
>>> )
>>> WHERE A.key IS NOT NULL
>> This is actually valid ANSI SQL syntax. Check out the non-core feature
>> T641, "Multiple column assignment".
>>
>>
>> /Jarl
>>
>
> Which ANSI/ISO version are you referring to? I don't see it in SQL 2003.
>
>

--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer