CODEWITHSUNDEEP
mysqlsql
user6064424
user6064424

Reputation:

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.

Upvotes: 3

Views: 129

Answers (2)

Chris Mc Cormick
Chris Mc Cormick

Reputation: 91

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

Upvotes: 2

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

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'

Upvotes: 1

Related Questions