You need to sign in to do that
Don't have an account?
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;
}
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
I think you are use inner join.?
Yes it is an inner join...
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.
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.