Can I update a group of records in a table from another group of records in the same table with one SQL statement?

[Bart Pietercil]

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