<html><body><div style="color:#000; background-color:#fff; font-family:lucida console, sans-serif;font-size:10pt"><div style="font-family: 'lucida console', sans-serif; font-size: 10pt; "><span>hi ruslan,</span></div><div style="font-family: 'lucida console', sans-serif; font-size: 10pt; "><br></div><div><span><span style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px; ">i believe the mysql syntax would be:</span></span></div><div><span><span style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px; "><br></span></span></div><div><span><span style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px; ">UPDATE t1 </span><br style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px; "><font face="'times new roman', 'new york', times, serif">INNER JOIN t2 ON </font></span><span style="font-family: 'times new roman', 'new york', times, serif; font-size:
16px; ">t1.id = t2.id </span></div><div><span style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px; ">SET t1.f1 = t2.f1 </span><br></div><div><font face="'times new roman', 'new york', times, serif"><br></font></div><div><font face="'times new roman', 'new york', times, serif">and the sqlserver version would be:</font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; "><br></span></font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; ">UPDATE t1 </span><br style="font-size: 16px; "><span style="font-size: 16px; ">SET t1.f1 = t2.f1 </span><br style="font-size: 16px; "><span style="font-size: 16px; ">FROM t1 </span><br style="font-size: 16px; "><span style="font-size: 16px; ">INNER JOIN t2 ON t1.id = t2.id </span><br style="font-size: 16px; "></font></div><div><font face="'times new roman', 'new
york', times, serif"><br></font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; ">i can't see this being used often, but i will need to use it when transitioning clients to valentina.</span></font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; ">each client (dozens) will have a minimum of 500k records needing this...and yes, it's 1:1.</span></font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; "><br></span></font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; ">thanks,</span></font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; ">george</span></font></div><div><font face="'times new roman', 'new york', times, serif"><span style="font-size: 16px; "><br></span></font></div><div><font face="'times new
roman', 'new york', times, serif"><span style="font-size: 16px; "><br></span></font></div><div><font face="'times new roman', 'new york', times, serif"><br></font></div><div></div><div style="font-size: 10pt; font-family: 'lucida console', sans-serif; "><div style="font-size: 12pt; font-family: 'times new roman', 'new york', times, serif; "><div dir="ltr"><font size="2" face="Arial"> <b><span style="font-weight:bold;">From:</span></b> Ruslan Zasukhin <ruslan_zasukhin@valentina-db.com><br> <b><span style="font-weight: bold;">To:</span></b> "valentina@lists.macserve.net" <valentina@lists.macserve.net> <br> <b><span style="font-weight: bold;">Sent:</span></b> Friday, March 2, 2012 12:14:38 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: update join<br> </font> </div> <br>On 3/2/12 5:58 PM, "George Parkinson" <<a ymailto="mailto:george@microtherapy.ca" href="mailto:george@microtherapy.ca">george@microtherapy.ca</a>>
wrote:<br><br>> hello all,<br>> i'm trying to do a mass update of a field in a table with the value of a<br>> similar field from another table, based on a common id.<br>> <br>> ie:<br>> UPDATE t1 <br>> SET t1.f1 = t2.f1 <br>> FROM t1 <br>> JOIN t2 ON t1.id = t2.id <br>> <br>> but this results in an error:<br>> Kernel error: 0x71000. line 2:7: expecting '=', found '.'<br><br><br>You have see such syntax in some db ?<br><br>> i've tried other variations but am obviously missing something (probably<br>> simple) anyone have a suggestion?<br><br>Hi George,<br><br>You need do this once for some db<br>or often ?<br><br><br>==========================<br>If once, yet exists way with loop<br><br> curs1 = db.SqlSelect( "SELECT T1.RecID1, .f1 FROM T1 join T2 ... )<br> <br> for each record<br> {<br>
tbl1.RecID = curs1.ULongField(1).value<br><br> tbl1.UlongField("id").value = curs1.ULongField(2).Value<br> tbl1.UpdateRecord()<br> }<br><br><br><br>==========================<br>IF you have Foreign Key defined on this T1,id = T2.id with name xxxPtr<br>Then you can use operator -><br><br>But it seems you talk not about normal Foreign Key 1:M<br>You say about some 1:1 I think ...<br>And ID - ID ... <br><br><br><br>-- <br>Best regards,<br><br>Ruslan Zasukhin<br>VP Engineering and New Technology<br>Paradigma Software, Inc<br><br>Valentina - Joining Worlds of Information<br><a href="http://www.paradigmasoft.com" target="_blank">http://www.paradigmasoft.com</a><br><br>[I feel the need: the need for speed]<br><br><br>_______________________________________________<br>Valentina mailing list<br><a ymailto="mailto:Valentina@lists.macserve.net"
href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br><a href="http://lists.macserve.net/mailman/listinfo/valentina" target="_blank">http://lists.macserve.net/mailman/listinfo/valentina</a><br><br><br> </div> </div> </div></body></html>