3

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.

Improve this question
3
  • 1
    What condition do you use to combine Table1 and Table2? Commented Jul 5, 2016 at 9:16
  • 2
    Read bout UPDATE...JOIN ... Commented Jul 5, 2016 at 9:17
  • 1
    Table2 ? You might need to join the two tables Commented Jul 5, 2016 at 9:17

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

you need to join table 1 and table2; then you can update 

UPDATE Table1 AS b
INNER JOIN Table2 AS g ON b.id = g.id SET Table1_fieldname1='field1_content', Table1_fieldname2='field2_content'
where Table1_fieldname3=2 AND g.fieldname='XXX'
Improve this answer

4 Comments

b.id is primary key of Table2 and g.id is primary key of Table1? Will this code still work if id column is not present in the tables?
no i just added it as example you have to join it using prefer column
Understand better now. I need to look at the foreign key relationship.
Yes, it works. Thanks. If you don't mind, I chose the other answer as the answer as it contains more explanations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.