How to update fields using another table

[Dasari Srihari]

Hi All,

I have 2 tables user table & personal data table. Personal email id available in personal data table available. Now i need to update personal email id in user table. In both tables employee id available based employee id i need to update personal email in user table. How we can achieve this..

[Sagar Pagar]

Hi @Dasari Srihari,

Scripts looks good to me. Make sure that field names are correct.

Thanks,

Sagar Pagar

[Sagar Pagar]

Hi @Dasari Srihari,

Great!. Yes, you need to execute same scripts to update existing records.

Please mark as helpful and correct, if I have been able to assist you in any way!
This will be useful for future readers.

Thanks,
Sagar Pagar

[timjohnson2584]

---

@Dasari Srihari wrote:

Hi All,

I have 2 tables user table & personal data table. Personal email id available in personal data table available. Now i need to update personal email id in user table. In both tables employee id available based employee id i need to update personal email in user table. How we can achieve this..

---

To update fields using another table, you can utilize   SQL's UPDATE statement with a JOIN operation. The specific syntax may vary slightly depending on the database management system you are using, but the general steps are as follows:

1. Understand the relationship: Identify the relationship between the two tables that will allow you to update the fields. Typically, there should be a common column between the tables that can be used to establish the relationship.

2. Write the UPDATE statement: Construct an UPDATE statement with a JOIN operation to update the desired fields. The syntax may look like this:

sqlCopy code

UPDATE table1 SET table1.field1 = table2.field2 FROM table1 JOIN table2 ON table1.common_column = table2.common_column WHERE condition;

In this example, table1 is the table you want to update, table2 is the table containing the new values, and common_column is the column that establishes the relationship between the two tables. field1 is the field in table1 that you want to update, and field2 is the corresponding field in table2 with the new values. You can also add a condition in the WHERE clause to further filter the rows to be updated if needed.

1. Execute the UPDATE statement: Run the SQL query to execute the UPDATE operation. This will update the specified fields in table1 using the corresponding values from table2 based on the established relationship.

Please note that the specific syntax and capabilities may vary depending on the database management system you are using. Consult the documentation or resources specific to your database for more details on the correct syntax and options available to you.

[BiancaK]

I had the same requirement which was to update fields on the project form based on a change made to a field on the demand form. 

When a demand user or manager updates a field on the demand record then this update data should roll up to the project form fields. 

To achieve this I created: 

Business Rule

Table: dmn_demand

When: after

Order: 100

Insert: checked

Update: checked

Add your specific filter conditions - here I have used sys_id != empty

On the Advanced tab I have created the below script:

(function executeRule(current, previous /*null when async*/ ) {

var cb = current.capital_budget;

var ob = current.operational_budget;

var gr = new GlideRecord('pm_project');

gr.addQuery('sys_id', current.project); //sys_id is the sys id of pm_project and current.project is the project field on the demand form

gr.query();

if (gr.next()) {

//Update records

gr.u_demand_due_date = current.requested_by; 

gr.u_demand_tshirt_size = current.size;

var demand_budget = parseFloat(cb) + parseFloat(ob)

gr.u_demand_budget_cost = demand_budget.toFixed(2); //2 decimal places

gr.update();

}

}

)(current, previous);