SQL Error(1054): Unknown column in 'where clause' when trying to update. Column is actually available I have two tables namely table_a and table_b.
Table_a has several columns including student_id and course_start_date
Table_b has several columns including student_id and fee_pay_date
student_id is INT field and course_start_date &amp; fee_pay_date are DATE fields
I want to update column course_start_date in table_a by reading fee_pay_date from table_b for each student.
When I write the following command in MariadB, it throws up an error:
UPDATE table_a 
SET table_a.course_start_date = table_b.fee_pay_date 
WHERE (table_a.student_id = table_b.student_id);

Error: SQL Error(1054): Unknown column 'table_b.student_id in 'where clause'

I have checked the tables. All column names are correct. Can anyone help on how to resolve this issue?
Thanks in advance.
That is not valid SQL - you are not referencing table_b in any FROM or JOIN clauses so can not use columns from it in SELECT, SET, WHERE or other parts.
Sticking to standards compliant SQL you need to use a sub-select here:
UPDATE table_a 
   SET table_a.course_start_date = (
           SELECT table_b.fee_pay_date
             FROM table_b
            WHERE table_b.student_id = table_a.student_id
            );

Some databases support using JOINs in UPDATE statements so avoid sub-selects. This can often be easier to read but will break if you need to support different database back-ends. For mySQL and MariaDB this would be something like:
UPDATE table_a 
  JOIN table_b 
       ON (table_a.student_id = table_b.student_id)
   SET table_a.course_start_date = table_b.fee_pay_date
       ;

Not that both of these statements will update every row in table_a even if the row already has the right value so could be very inefficient if only a few rows actually need updating. This is by design, as you might want triggers to fire and so forth even for a NoOp update. To avoid this if it is not desirable add a WHERE clause to block unneeded updates:
UPDATE table_a 
  JOIN table_b 
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
 WHERE table_a.course_start_date &lt;&gt; table_b.fee_pay_date
    OR (table_a.course_start_date IS     NULL AND table_b.fee_pay_date IS NOT NULL)
    OR (table_a.course_start_date IS NOT NULL AND table_b.fee_pay_date IS     NULL)
       ;

or:
UPDATE table_a 
  JOIN table_b 
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
 WHERE COALESCE(table_a.course_start_date, '9999-01-01') &lt;&gt; COALESCE(table_b.fee_pay_date, '9999-01-01');

(you can simplify those WHERE clauses a bit if table_b.fee_pay_date can never be NULL)


As a related side-issue: to illustrate the concern of using non-standard syntax, the SQL Server equivalent to
UPDATE table_a 
  JOIN table_b 
       ON table_a.student_id = table_b.student_id
   SET table_a.course_start_date = table_b.fee_pay_date
       ;

would be
UPDATE table_a 
   SET course_start_date = table_b.fee_pay_date
  FROM table_a
  JOIN table_b 
       ON table_a.student_id = table_b.student_id
       ;

and in postgres you would need:
UPDATE table_a 
   SET course_start_date = table_b.fee_pay_date
  FROM table_b 
 WHERE table_a.student_id = table_b.student_id
       ;

Not vastly different, but if you want to support multiple databases or later move from one to another, you need to be careful of this sort of difference. The sub-query version should behave the same in all three (and most other places).