Can I update a group of records in a table from another group of records in the same table with one SQL statement?
Sims, John (CDC/CCHP/NCBDDD) (CTR)
ayu8 at cdc.gov
Thu Sep 17 09:44:56 CDT 2009
Hi Bart,
Oh, I'd definitely agree that a stored proc would be more efficient for
this. Just trying to help out conceptually :-)
-John
-----Original Message-----
From: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] On Behalf Of Bart
Pietercil
Sent: Thursday, September 17, 2009 10:31 AM
To: Valentina Developers
Subject: Re: Can I update a group of records in a table from another
group of records in the same table with one SQL statement?
Wow John,
great explanation, but why not make a stored procedure (like Ruslan
suggested) that looks like this:
watch it pseudocode
create or replace procedure UpdateARecordSet()
begin
declare aRecid llong;
declare x,y string;
declare mycursor cursor for select recid, x,y from the records I want to
change open mycursor; fetch first mycursor into aRecid, x,y; loop begin
update tbl_x set fld1 = x,fld2= y where recid = aRecid; exception
when loop_is_finished
exit loop
when others
raise
end
close mycursor
end
Don't forget, pseudo code on the top of my head, but I hope you get the
idea.
hth
Bart Pietercil
On 17 Sep 2009, at 15:44, Sims, John (CDC/CCHP/NCBDDD) (CTR) wrote:
>
> Hi Barry,
>
> -----Original Message-----
>> From: valentina-bounces at lists.macserve.net
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of Barry G.
> Sumpter
>> Sent: Wednesday, September 16, 2009 11:56 PM
>> To: 'Valentina Developers'
>> Subject: Can I update a group of records in a table from another
>> group
> of records in the same table with one SQL statement?
>
>> Hi Ruslan,
>
>> About two years ago you assisted me in writing a single sql statement
> to copy 100 records into a table from the same table.
>
>> I'd now like to update those records from data from the same table.
>
>> Is this allowed in Valentina?
>
>
>> UPDATE myTable
>> ( myField1, myField2, myField3, myField4 ) WHERE myTable.myField1 =
> 'UpdateTheseRecordsSelectionCriteria1'
>> SELECT myField1, myField2, myField3, myField4 FROM myTable WHERE
>> myTable.Field1 = 'WithDataFromTheseRecordsSelectionCriteria2';
>
>> Thanks and all the best,
>> Barry G. Sumpter
>
> ----------------------------------
>
> You can do something similar to what you are asking but it is
> necessary to have some kind of field that provides an internal
> relation between records. The problem with your query is you are
> selecting one set of data then asking the database to update it with
> another set of data but the database doesn't know which record in the
> second set should affect which record(s) in the first set. I've
> created an example simliar to yours to show what will work (NOTE: I'm
> at work and had to use SQL Server to create the example so (1) the
> syntax may not be the exact same as Valentina and (2) you'll have to
> check with Ruslan to see if Valentina supports such a query (though
> I'm pretty sure I've done this before in Valentina).
>
> Here's the initial data in a table called "myTable" (tab delimited)
>
> keyField myField2 myField3 conditionField
> commonIdentifier
> 1 1 1 1 1
>
> 2 2 2 2 2
>
> 3 3 3 1 3
>
> 4 4 4 2 1
>
> 5 4 5 1 2
>
> 6 5 6 2 3
>
>
> I want to set the values of myField2 and myField3 for the records
> where the conditionField=1 to the corresponding values of myField2 and
> myField3 where the conditionField=2. Notice the commonIdentifier
> field.
> This is the "internal relation" field between the records (you could
> certainly use multiple fields to define the internal relation). So,
> keyFields 1,3, and 5 should be updated with the values of keyFields 4,
> 6, and 2 respectively.
>
> So, here's the update query:
>
> update mt1 set mt1.myField2=mt2.myField2, mt1.myField3=mt2.myField3
> from mytable mt1, mytable mt2 where
> mt1.commonIdentifier=mt2.commonIdentifier
> and mt1.conditionField=1
> and mt2.conditionField=2
>
> "mt1" and "mt2" are table aliases. I know Valentina supports these
> but I think the syntax might be different.
>
> The result of the query looks like this (tab delimited):
>
> keyField myField2 myField3 conditionField
> commonIdentifier
> 1 4 4 1 1
>
> 2 2 2 2 2
>
> 3 5 6 1 3
>
> 4 4 4 2 1
>
> 5 2 2 1 2
>
> 6 5 6 2 3
>
>
>
> This is just a 1-to-1 update of values. It is also possible to do a
> 1-to-many update as long as the field(s) you are using to define the
> internal relation is unique across all values of the set you are
> pulling the values from. Here is a less verbose example of that:
>
> Starting data (notice the commonIdentifier value of 7 for keyFields
> 1,3,4, and 5):
>
> keyField myField2 myField3 conditionField
> commonIdentifier
> 1 1 1 1 7
>
> 2 2 2 2 2
>
> 3 3 3 1 7
>
> 4 4 4 2 7
>
> 5 5 5 1 7
>
> 6 6 6 2 3
>
>
> Again, I want to update myField2 and myField3 where the
> conditionField=1
> to the corresponding value where the conditionField = 2. So,
> keyFields 1,3, and 5 will all have the values of keyField 4 (all are
> linked by the
> commonIdentifier=7)
>
> NOTE: This is the same query as above.
>
> update mt1 set mt1.myField2=mt2.myField2, mt1.myField3=mt2.myField3
> from mytable mt1, mytable mt2 where
> mt1.commonIdentifier=mt2.commonIdentifier
> and mt1.conditionField=1
> and mt2.conditionField=2
>
> And here are the results:
>
> keyField myField2 myField3 conditionField
> commonIdentifier
> 1 4 4 1 7
>
> 2 2 2 2 2
>
> 3 4 4 1 7
>
> 4 4 4 2 7
>
> 5 4 4 1 7
>
> 6 6 6 2 3
>
>
> To my knowledge, many-to-many updates are just not possible. There
> just isn't any way for the database to know who updates who.
>
> I hope this helps you (and that I don't have any typos that are going
> to lead to utter confusion)
>
> -John
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina
More information about the Valentina
mailing list