0

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 & 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.

1

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 <> 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') <> 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).

| improve this answer | |
  • Thank you very much David for your help. Your solution worked very nicely. – jyotidutta87 Oct 29 at 18:37
0

Your Update statement is missing a reference to table_b. The statement should be in the form of

UPDATE table_a, table_b
SET 
      table_a.course_start_date = table_b.fee_pay_date 
WHERE 
      table_a.student_id = table_b.student_id;

The Mariab Update and Adding/Changing data documentation provides some good examples.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.