Lit Aiy Lit Aiy - 4 months ago 7
MySQL Question

MySQL Update statement after adding extra condition at WHERE

I have this MySQL Update statement. It works fine.

UPDATE Table1
SET Table1_field1='field1_content', Table1_field2='field2_content'
where Table1_field3=2


All the fields above belong to the same table. I then added an extra condition
AND Table2.fieldname='XXX'
to the WHERE clause

UPDATE Table1
SET Table1_fieldname1='field1_content', Table1_fieldname2='field2_content'
where Table1_fieldname3=2 AND Table2.fieldname='XXX'


This time, the SQL statement fails. The error is "unknown column
Table2.fieldname
in where clause". However,
Table2.fieldname
does exist.

Answer

In order to be able to use fields from Table2 in your query you'll need use a JOIN between Table1 and Table2.

A JOIN effectively combines a row from each table into a single row for your query, based on a provided condition. For example if both Table1 and Table2 have a column tableID, we can combine rows from each table where the tableIDs match. The query would then look like below:

UPDATE Table1
JOIN Table2
    ON Table1.tableID = Table2.tableID
SET Table1_fieldname1='field1_content', Table1_fieldname2='field2_content'
WHERE Table1_fieldname3=2 AND Table2.fieldname='XXX';

The JOIN keyword is equivalent to INNER JOIN. There are different types of JOINs available and I'd recommend reading up about them. Here's a reference image to give you an idea of the different types: enter image description here

Comments