How do I use INNER JOIN in an UPDATE statement?

Question

I have 2 mysql tables that are joined, in the database, by the descriptionId field.

dataTable.dataId
dataTable.descriptionId
dataTable.userId
dataTable.value

descriptionLookupTable.descriptionId
descriptionLookupTable.descriptionName

In PHP, I am trying to update the field: dataTable.value, when dataTable.descriptionID = Gender and the dataTable.userId = $_SESSION['userId'].

For example in the descriptionLookupTable: descriptionLookupTable.descriptionId = 1 and descriptionLookupTable.descriptionName = Gender. And in the dataTable: dataTable.descriptionID = 1.

I understand the part that deals with the userID by using:

"WHERE dataTable.userId = '" . $_SESSION['userId'] . "'";

But I can't figure out how to use INNER JOIN.

The following query does not work.

$query = "UPDATE dataTable
  INNER JOIN descriptionLookupTable
  ON dataTable.descriptionId = descriptionLookupTable.descriptionId
  AND descriptionLookupTable.descriptionName = Gender
  SET Gender = '$gender' WHERE dataTable.user_id = '" . $_SESSION['user_id'] . "'";

The error message is: Unknown column 'Gender' in 'field list'.

http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables — Pedro Monteiro, Jan 14 '14 at 15:51
Thanks but when I try it I get Error msg: Unknown column 'dataTable.Gender' in 'field list — user2646006, Jan 14 '14 at 16:13

score 1 · Accepted Answer · answered Jan 14 '14 at 15:58

1

Gender apparently is not a column in your table. One of the field must be named Gender for you to be able to update its content

what about trying to update a field which is in your table :) :

SET dataTable.value = '$gender' WHERE dataTable.user_id = '" . $_SESSION['user_id'] . "'";

answered Jan 14 '14 at 15:58

Félix Adriyel Gagnon-Grenier

8,422
10
52
65

Thanks but when I try it I get Error msg: Unknown column 'dataTable.value' in 'field list'. – user2646006 Jan 14 '14 at 16:15
hmmm. how come mysql would recognize dataTable.descriptionId for the join but not dataTable.value. are you sure about capitalization and the name of your fields? could you post the structure of your tables? like the output of phpmyadmin when you export the structure only to the screen – Félix Adriyel Gagnon-Grenier Jan 14 '14 at 16:21
oh and be sure to put gender between colons 'gender' as VolkerK said. this is effectively a problem which you will run into after you rule out the problem of column names – Félix Adriyel Gagnon-Grenier Jan 14 '14 at 16:25
1

Thank you to everyone! The combination of ideas from Felix and Volkerk worked! The following mysql statement worked: $query = "UPDATE dataTable INNER JOIN descriptionLookupTable ON dataTable.descriptionId = descriptionLookupTable.descriptionId AND descriptionLookupTable.descriptionName = 'Gender' SET dataTable.value = '$gender' WHERE dataTable.user_id = '" . $_SESSION['user_id'] . "'"; – user2646006 Jan 14 '14 at 16:27
Actually there is a typo in my last comment wrt user_id. The following is the fix: $query = "UPDATE dataTable INNER JOIN descriptionLookupTable ON dataTable.descriptionId = descriptionLookupTable.descriptionId AND descriptionLookupTable.descriptionName = 'Gender' SET dataTable.value = '$gender' WHERE dataTable.userId = '" . $_SESSION['userId'] . "'"; – user2646006 Jan 14 '14 at 16:39

score 0 · Answer 2 · answered Jan 14 '14 at 15:52

0

descriptionLookupTable.descriptionName = Gender

Gender in this case is a string literal, therefore you have to mark it as such

descriptionLookupTable.descriptionName = 'Gender'

answered Jan 14 '14 at 15:52

VolkerK

95,432
20
163
226

Yes, I tried that, but I get the same answer. Thanks though. – user2646006 Jan 14 '14 at 16:11
no idea why that would be voted down. you need those colons for sql to work, even though this is not the first problem that will appear from the query – Félix Adriyel Gagnon-Grenier Jan 14 '14 at 16:26

How do I use INNER JOIN in an UPDATE statement?

2 Answers2