Complex sql update

Hi,
I'm trying to update a key in tablea with tableb using the where there
are mulitple where criteria. I'm trying to avoid the sql cursors to do
update for each record.
Is there a way I can do that in the following statement:
update billing_detail_debit_card set bdp_id =
(
select b.bdp_id from billing_detail_participant b join
billing_detail_debit_card dc
on b.billing_proc_no = dc.billing_proc_no
and b.cust_no = dc.cust_no
and b.company_no = dc.company_no
and b.participant_id = dc.participant_id
)
where billing_detail_debit_card.billing_proc_no
(
select dc.billing_proc_no, dc.cust_no, dc.company_no,
dc.participant_id
from billing_detail_participant b join billing_detail_debit_card dc
on b.billing_proc_no = dc.billing_proc_no
and b.cust_no = dc.cust_no
and b.company_no = dc.company_no
and b.participant_id = dc.participant_id
) bdp_table
= bdp_table.billing_proc_no
and billing_detail_debit_card.cust_no = bdp_table.cust_no
and billing_detail_debit_card.company_no = bdp_table.company_no
and billing_detail_debit_card.participant_id = bdp_table.participant_id--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You're working overtime. You had it, but then added too much. Try
this:
UPDATE billing_detail_debit_card
SET bdp_id =
(SELECT bdp_id
FROM billing_detail_participant b
WHERE billing_proc_no = billing_detail_debit_card.billing_proc_no
AND cust_no = billing_detail_debit_card.cust_no
AND company_no = billing_detail_debit_card.company_no
AND participant_id = billing_detail_debit_card.participant_id )
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRJITJoechKqOuFEgEQKrDgCghArCBOaCx7BJ
OfDlVboQI9nUx5AAn1nA
vGO7K01Trgrw9KG7UHn7Kw2q
=XJiT
--END PGP SIGNATURE--
dmalhotr2001@.yahoo.com wrote:
> Hi,
> I'm trying to update a key in tablea with tableb using the where there
> are mulitple where criteria. I'm trying to avoid the sql cursors to do
> update for each record.
> Is there a way I can do that in the following statement:
> update billing_detail_debit_card set bdp_id =
> (
> select b.bdp_id from billing_detail_participant b join
> billing_detail_debit_card dc
> on b.billing_proc_no = dc.billing_proc_no
> and b.cust_no = dc.cust_no
> and b.company_no = dc.company_no
> and b.participant_id = dc.participant_id
> )
> where billing_detail_debit_card.billing_proc_no
> (
> select dc.billing_proc_no, dc.cust_no, dc.company_no,
> dc.participant_id
> from billing_detail_participant b join billing_detail_debit_card dc
> on b.billing_proc_no = dc.billing_proc_no
> and b.cust_no = dc.cust_no
> and b.company_no = dc.company_no
> and b.participant_id = dc.participant_id
> ) bdp_table
> = bdp_table.billing_proc_no
> and billing_detail_debit_card.cust_no = bdp_table.cust_no
> and billing_detail_debit_card.company_no = bdp_table.company_no
> and billing_detail_debit_card.participant_id = bdp_table.participant_id
>|||Be careful with this. It updates every row of billing_detail_debit_card,
which might result in setting bdp_id to NULL for many
rows in billing_detail_debit_card you don't wish to update,
since if the subquery does not yield any rows, its value will
be NULL.
UPDATES like this can often benefit from SQL Server's
proprietary UPDATE .. FROM syntax, which might look
something like this for the query you have:
UPDATE billing_detail_debit_card
FROM billing_detail_participant AS b
WHERE b.billing_proc_no = billing_detail_debit_card.billing_proc_no
AND b.cust_no = billing_detail_debit_card.cust_no
AND b.company_no = billing_detail_debit_card.company_no
AND b.participant_id = billing_detail_debit_card.participant_id
Steve Kass
Drew University
"MGFoster" <me@.privacy.com> wrote in message
news:0rokg.6390$lf4.1883@.newsread1.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> You're working overtime. You had it, but then added too much. Try
> this:
> UPDATE billing_detail_debit_card
> SET bdp_id =
> (SELECT bdp_id
> FROM billing_detail_participant b
> WHERE billing_proc_no = billing_detail_debit_card.billing_proc_no
> AND cust_no = billing_detail_debit_card.cust_no
> AND company_no = billing_detail_debit_card.company_no
> AND participant_id = billing_detail_debit_card.participant_id )
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRJITJoechKqOuFEgEQKrDgCghArCBOaCx7BJ
OfDlVboQI9nUx5AAn1nA
> vGO7K01Trgrw9KG7UHn7Kw2q
> =XJiT
> --END PGP SIGNATURE--
>
> dmalhotr2001@.yahoo.com wrote:|||or you can add a where exists clause to update on rows where a matching row
exists.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e628WzOkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> Be careful with this. It updates every row of billing_detail_debit_card,
> which might result in setting bdp_id to NULL for many
> rows in billing_detail_debit_card you don't wish to update,
> since if the subquery does not yield any rows, its value will
> be NULL.
> UPDATES like this can often benefit from SQL Server's
> proprietary UPDATE .. FROM syntax, which might look
> something like this for the query you have:
> UPDATE billing_detail_debit_card
> FROM billing_detail_participant AS b
> WHERE b.billing_proc_no = billing_detail_debit_card.billing_proc_no
> AND b.cust_no = billing_detail_debit_card.cust_no
> AND b.company_no = billing_detail_debit_card.company_no
> AND b.participant_id = billing_detail_debit_card.participant_id
> Steve Kass
> Drew University
>
> "MGFoster" <me@.privacy.com> wrote in message
> news:0rokg.6390$lf4.1883@.newsread1.news.pas.earthlink.net...
>|||Sorry, forgot the code...
update billing_detail_debit_card
set bdp_id =
(
select b.bdp_id from billing_detail_participant as b
where b.billing_proc_no = billing_detail_debit_card.billing_proc_no
and b.cust_no = billing_detail_debit_card.cust_no
and b.company_no = billing_detail_debit_card.company_no
and b.participant_id = billing_detail_debit_card.participant_id
)
where exists
(
select b.bdp_id from billing_detail_participant as b
where b.billing_proc_no = billing_detail_debit_card.billing_proc_no
and b.cust_no = billing_detail_debit_card.cust_no
and b.company_no = billing_detail_debit_card.company_no
and b.participant_id = billing_detail_debit_card.participant_id
)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:Ol9$tdUkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> or you can add a where exists clause to update on rows where a matching
row
> exists.
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:e628WzOkGHA.2200@.TK2MSFTNGP05.phx.gbl...
billing_detail_debit_card,[color=darkred
]
there
do
bdp_table.participant_id
>

Complex query with IN

A simple query using IN would be something like:
SELECT * FROM foo
WHERE fld1 IN (SELECT fld1 FROM bar)
No problem. But what if the search criteria are more complex, for example,
matching against multiple columns? What I'd like to do is something like:
SELECT * FROM foo
WHERE fld1, fld2 IN (SELECT fld1, fld2 FROM bar)
but that is not valid SQL. How would I write it? Basically, for each row
in bar, I want to use fld1 and fld2 to select 1 (or more) rows from foo.
Any thoughts and help are greatly appreciated
On Thu, 14 Oct 2004 07:43:38 -0400, Andy Walldorff wrote:

>A simple query using IN would be something like:
(snip)
> What I'd like to do is something like:
>SELECT * FROM foo
>WHERE fld1, fld2 IN (SELECT fld1, fld2 FROM bar)
>but that is not valid SQL.
Hi Andy,
You can use the following equivalent:
SELECT Col1, Col2, ..., ColN
FROM foo
WHERE EXISTS (SELECT *
FROM bar
WHERE bar.fld1 = foo.fld1
AND bar.fld2 = foo.fld2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo, I'll give it a try
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:peusm0dnqvss43p32cne98gcekf3cjutnv@.4ax.com...
> On Thu, 14 Oct 2004 07:43:38 -0400, Andy Walldorff wrote:
> (snip)
> Hi Andy,
> You can use the following equivalent:
> SELECT Col1, Col2, ..., ColN
> FROM foo
> WHERE EXISTS (SELECT *
> FROM bar
> WHERE bar.fld1 = foo.fld1
> AND bar.fld2 = foo.fld2)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Complex Data Forms

I could really use some suggestions on the best way to get the results of a pretty complex aspx form. There are a whole bunch of search criteria. Most of the criteria are ranges of numbers such as a starting number and ending number but other criteria need to search text fields for keywords. Most fields are optional which means that many values will not be submitted. Not that there is a right and wrong way to do it, but what typically works out better for these things, an aspx procedure that puts together a highly complicated select statement or writing a highly complicated stored procedure? Either way I see a ton of if statements, or am I making this a lot harder then it needs it needs to be?

Thanks.In this type of case, I pass all the parameters to a stored procedure and then based on which ones are filled in, I create a dynamic SQL string that includes all the parameters in the where clause. Then execute the string. If you have a lot of parameters, you'd have to build a lot of statements to cover all the possibilitites. A dynamic SQL string is much easier to maintain, change, etc.|||Previously I've passed the params in as a single text column ecoded as XML and use OPENXML in the stored proc. It doesn't save on the IFs in the proc though :( Although depending upon your query (i.e is it ANDs and ORs on the params?) you can create a "criteria table" from the XML and simply join on the criteria data.