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)]

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