Update field in one table using data from another based on a key value

Applications Database Connections
#1

I need to update the Remark field in my table ‘student’ by concatenating the text data with the Remark field in my secondary table Problem_Student by matching the Student_reg in both tables.
so i add a event onValidateSuccess

// SQL statement parameters
$update_table = ‘student’; // Table name
$update_where = “student_reg = ‘student_reg’”; // Where clause
$update_fields = array( Remark );

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);

but got error

Error
Use of undefined constant Remark - assumed ‘Remark’
Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE student_reg = ‘student_reg’’ at line 1

please i’m new to this

#2

Remark is kind of strange here… $Remark for a normal variable or {Remark} if you want to use the field data would make more sense.

#3

Actually the main thing is i created a table for students registration(tblStudent) in this table(page) there is a field for Remark. i also created a table(page) for adding remark(tblprboble_student) since it cant be added manually from the student registration page. i want the data from remark field in Problem_student to save both in Problem_student table under remark field and also in student registration table under remark field… like i said earlier im new to this

#4

Ah you want to same the date in two tables. Well then you’d use the OnAfterUpdate event on the remark field and simply perform the update as you already have figured out.
But if you want to access the contents of a field in scriptcase you have to put { } around it.

#5

Thanks bro, but can you help in giving a detail explanation like re-writing the code… still learning

#6

Thanks bro. but after adding the {} around remark like this
// SQL statement parameters
$update_table = ‘student’; // Table name
$update_where = “student_reg = ‘student_reg’”; // Where clause
$update_fields = array( {Remark} );

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);

i got this error

Error
Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Malpratice WHERE student_reg = ‘student_reg’’ at line 1

#7

Hi,
I assume you are working in a single record form.
The error is triggered by the WHERE-CLAUSE.
If I understood your problem, the basic statement should read like this:

UPDATE student s, problem_student p
SET s.remark = CONCAT(s.remark, p.remark)
WHERE s.student_reg = p.student_reg AND s.student_reg = {student_reg}

You might need to adjust it to your specific needs.

jsb