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 copy routine, mulitple tables and changing GUIDs

hello,

I have several tables that have guids as their primary keys and the tables are related as follows:

Table1 - primary key =ServiceNo (Guid), Filter Key =CampaignNo

Table2 - primary key = CostBasisNo (Guid), Foreign Key =ServiceNo (from Table1)

Table3 - primary key = UserId, Foreign Key =ServiceNo (from table1)

Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)

what I need to do is copy all records from Table1 where CampaignNo = @.CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.

The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1

This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's forSourceServiceNo and the relatedMemberServiceNo which all would have changed.

I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.

regards

DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

INSERT INTO Table1 (CampaignNo) -- and other columns as well, I guess
SELECT @.newCampaignNo FROM Table1 WHERE ServiceNo=@.oldCampaignNo

INSERT INTO Table2 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

INSERT INTO Table3 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

I suppose table4 will be quite easy as well, but you need to describe where the ServiceNo:s come from.

|||

Hi gunteman,

thank you for replying, I'm not sure that will do what I want.

In my db the CampaignNo will never equal the ServiceNo.

Maybe if I try and explain the problem another way and only use two related tables

Lets say I have:

Table1 where the primary key =ServiceNo, and other attributes including anon primary key = CampaignNo

My first task is to copy all the rows from Table1 where an input parameter called @.CampaignNo = CampaignNo,

So my statement would be something like:

INSERT INTO Table1 (ServiceNo, attribute1, attribute2,CampaignNo)
(SELECTNEWID(), attribute1, attribute2, @.CampaignNo from Table1 WHERE
CampaignNo = @.CampaignNo)

NEWID() of course will create a new uniqueidentifier.

now this works fine, I've tried this out using a temporary table and all is good.

my problem comes when I need to copy the contents of the related table, say Table2,

Lets say Table2 hasCostBasisNo (primary key), attribute1, attribute2,ServiceNo (foreign key from Table1)

now I need to copy rows from Table2 but where the ServiceNo = the old ServiceNo from Table1 and I guess this is where the list of service numbers again = @.CampaignNo.

I guess I could say something like

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2, ServiceNo)
(SELECTNEWID(), attribute1, attribute2, ServiceNo from Table2 WHERE
?)

and this is where I hit the problem, how do I write the INSERT statement for Table2 to copy it's own records but only where the ServiceNo = the old ServiceNo from Table1 and then insert the associated new ServiceNo.

hope that's made the issue a little clearer, the other tables don't really matter cos if I can solve this the others will follow.

regards

|||DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

DECLARE @.conversion TABLE
(
oldServiceNo uniqueidentifier,
newServiceNo uniqueidentifier
)

INSERT INTO @.conversion (oldServiceNo,newServiceNo)
SELECT ServiceNo,newid() FROM Table1 WHERE CampaignNo=@.oldCampaignNo

INSERT INTO Table1 (ServiceNo,attribute1, attribute2, CampaignNo)
SELECT newServiceNo, attribute1, attribute2, @.newCampaignNo FROM @.conversion c,Table1 t WHERE c.oldServiceNo=t.ServiceNo

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2,ServiceNo)
SELECT newid(), attribute1, attribute2, newServiceNo FROM Table1 @.conversion c,Table2 t WHERE c.oldServiceNo=t.ServiceNo

..and so on.|||

Yes, that does the job very nicely indeed - thank you, it's also given me a pointer to other tables I need to copy the contents of.

nice one!