Clarity

We have introduced a new field (lookup) I need to write a sql statement to automatically update the values based on a condition. How can I achieve that?

i will suggest xog to update lookup field based on your business logic which you can fetch as sql select statement in gel script process which can be auto trigger using some start condition.

ok but what is the syntax for the sql statement? that is my question.

i will suggest you to check GEL Scripting site for syntax  

<!-- QUERY FOR MULTIPLE RESULTS -->
<sql:query escapeText="0" var="multipleResults">
     <![CDATA[
SELECT FULL_NAME
FROM SRM_RESOURCES
]]>
</sql:query>

<core:forEach items="${multipleResults.rows}" trim="true" var="row">
     <core:set value="${row.FULL_NAME}" var="firstColumn"/>
</core:forEach>
<!-- CHOOSE PREFERENCE -->
<core:forEach items="${multipleResults.rowsByIndex}" trim="true" var="row">
     <core:set value="${row[0]}" var="firstColumn"/>
</core:forEach>