update join

[George Parkinson]

hi ruslan,

i believe the mysql syntax would be:

UPDATE t1 
INNER JOIN t2 ON t1.id = t2.id  
SET t1.f1 = t2.f1 


and the sqlserver version would be:

UPDATE t1 
SET t1.f1 = t2.f1 
FROM t1 
INNER JOIN t2 ON t1.id = t2.id  


i can't see this being used often, but i will need to use it when transitioning clients to valentina.
each client (dozens) will have a minimum of 500k records needing this...and yes, it's 1:1.

thanks,
george



From: Ruslan Zasukhin <ruslan_zasukhin at valentina-db.com>
To: "valentina at lists.macserve.net" <valentina at lists.macserve.net> 
Sent: Friday, March 2, 2012 12:14:38 PM
Subject: Re: update join
 
On 3/2/12 5:58 PM, "George Parkinson" <george at microtherapy.ca> wrote:

> hello all,
> i'm trying to do a mass update of a field in a table with the value of a
> similar field from another table, based on a common id.
> 
> ie:
> UPDATE t1 
> SET t1.f1 = t2.f1 
> FROM t1 
> JOIN t2 ON t1.id = t2.id  
> 
> but this results in an error:
> Kernel error: 0x71000. line 2:7: expecting '=', found '.'


You have see such syntax in some db ?

> i've tried other variations but am obviously missing something (probably
> simple) anyone have a suggestion?

Hi George,

You need do this once for some db
or often ?


==========================
If once, yet exists way with loop

    curs1 = db.SqlSelect( "SELECT T1.RecID1,  .f1  FROM T1 join T2 ... )
      
    for each record
    {
           tbl1.RecID = curs1.ULongField(1).value

           tbl1.UlongField("id").value = curs1.ULongField(2).Value
           tbl1.UpdateRecord()
    }



==========================
IF you have Foreign Key defined on this T1,id = T2.id with name xxxPtr
Then you can use operator ->

But it seems you talk not about normal Foreign Key 1:M
You say about some 1:1  I think ...
And ID - ID ... 



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]


_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20120302/0efd96f8/attachment.html>