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
>

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
JasonHello,
Take a look into :-
http://www.sql-server-performance.c...pare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/ar...11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
Jason
Hello,
Take a look into :-
http://www.sql-server-performance.com/sql_data_compare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>
|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.
|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
JasonHello,
Take a look into :-
http://www.sql-server-performance.com/sql_data_compare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com