+ Start a Discussion
Shahin__cShahin__c 

SOQL update statement - Joining tables

Hi Guys,

I like to be able to write a SOQL query which is equivalent to SQL query below:

 

UPDATE

                A

SET

                A.col1 = B.col1

FROM

                TABLEA A

JOIN

                TABLE B

ON

                A.col2 = B.col2

                AND

                A.col3 = B.col3

 

So basically, I like to update table A using a some columns from table B where the join condition between the two tables satisfy. I know that one possible workaround is by looping through all rows from table B and testing for matching condition and then looping and setting values then updating the whole lot as below.

The problem with this approach is that I hit the governor limit very quickly (I have +500 rows in table A and +12,000 rows in table B). I am hoping that there is an alternative workaround for this to avoid the limits.

 

for (SObject1 tempA:A)

{

LIST<SObject2> LB = [SELECT

col1, col2,col3

FROM B

WHERE col2 =: tempA.col2 AND col3 = tempA.col3 ];

 

// NOW LOOP THROUGH THE SELECTED OBJECTS AND SET THE CORRECT VALUS

for (SObject2 tempB: LB)

{
tempB.col1 = tempA.SurchargeRate;
}

// NOW WE CAN UPDATE THE WHOLE LOT

update LB;

}

Best Answer chosen by Shahin__c
kevindotcarkevindotcar

Hi,
Well, there are a few things going on here.
SOQL is not SQL - Especially when it comes to joining objects (not tables).
It takes a little bit of getting ones head around SOQL when coming from
an SQL background.

You have to specifically have a lookup (or master-detail) relationship
between objects to 'join' objects.


I'd create a formula column that is the combination of col2 and col3 on both

objects, and then, let's say have col4 be a reference from object A to object B.

 

for (SObject1 tempA: [SELECT col1, col4.SurchargeRate FROM A WHERE colx == col4.colx] )
   col1 =  col4.SurchargeRate;


This is a total hack - because there might be garbage data in the columns that mes up your formula field.

 

...But after you get used to SF objects, you get out of the "table" mindset - and
begin to just make references to everything.  

Two good articles are here:
http://www.salesforce.com/us/developer/docs/api/Content/relationships_among_objects.htm
http://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/

 

AFAIK, there is no way to join SF objects on multiple columns... I just don't think things

work that way, but as soon as I say something like that, I'm sure someone will come up

with a way to do it.

 

Hope this helps -  I'm shooting from the hip but the above article references should

help.

 

All Answers

STest123STest123

I think you are use inner join.?

Shahin__cShahin__c

Yes it is an inner join...

kevindotcarkevindotcar

Hi,
Well, there are a few things going on here.
SOQL is not SQL - Especially when it comes to joining objects (not tables).
It takes a little bit of getting ones head around SOQL when coming from
an SQL background.

You have to specifically have a lookup (or master-detail) relationship
between objects to 'join' objects.


I'd create a formula column that is the combination of col2 and col3 on both

objects, and then, let's say have col4 be a reference from object A to object B.

 

for (SObject1 tempA: [SELECT col1, col4.SurchargeRate FROM A WHERE colx == col4.colx] )
   col1 =  col4.SurchargeRate;


This is a total hack - because there might be garbage data in the columns that mes up your formula field.

 

...But after you get used to SF objects, you get out of the "table" mindset - and
begin to just make references to everything.  

Two good articles are here:
http://www.salesforce.com/us/developer/docs/api/Content/relationships_among_objects.htm
http://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/

 

AFAIK, there is no way to join SF objects on multiple columns... I just don't think things

work that way, but as soon as I say something like that, I'm sure someone will come up

with a way to do it.

 

Hope this helps -  I'm shooting from the hip but the above article references should

help.

 

This was selected as the best answer
Shahin__cShahin__c

Thanks very much Kevin. The two articles are helpful. At least I now understand that DML statements and SOQL statements are 2 completely different things - unlike SQL. So I cannot modify data via SOQL. I guess I will need to make my query “more intelligent” to reduce the number of loops. I may look into Dynamic SOQL.