Update on inner join - looks good to me, where did I go wrong?

I'm trying to update on an join, but I can't find my error:

UPDATE `userTable`
SET `someField`="Jimmy Page"
FROM `userTable` INNER JOIN `anotherTable`
ON `userTable.userid`=`anotherTable.userid`
WHERE `userTable.someField`="Jim Morrison"
AND `anotherTable.date` < NOW();

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'FROM

That error is for reserved words, and I am escaping all the fields and
tables (using the backticks). So why the error?

This is on a CentOS 4 or 5 server, with MySQL 5.0.77, accessed from
the CLI. Thanks!


--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Update on inner join - looks good to me, where did I go wrong?

Now that I've got the syntax right, MySQL is complaining that a field
does not exist, which most certainly does:

mysql> UPDATE
-> `userTable`
-> INNER JOIN `anotherTable`
-> ON `userTable.userid`=`anotherTable.userid`
-> SET `userTable.someField`="Jimmy Page"
-> WHERE `userTable.someField`="Jim Morrison"
-> AND `anotherTable.date` < NOW();
ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list'
mysql>
mysql> SELECT count(someField) FROM userTable;
+---------------+
| count(someField) |
+---------------+
| 5076 |
+---------------+
1 row in set (0.00 sec)

mysql>

What could be the issue here? Thanks!


--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Update on inner join - looks good to me, where did I go wrong?

`userTable.userid` => `userTable`.`userid`

/ Carsten

On 09-09-2011 23:01, Dotan Cohen wrote:
> Now that I've got the syntax right, MySQL is complaining that a field
> does not exist, which most certainly does:
>
> mysql> UPDATE
> -> `userTable`
> -> INNER JOIN `anotherTable`
> -> ON `userTable.userid`=`anotherTable.userid`
> -> SET `userTable.someField`="Jimmy Page"
> -> WHERE `userTable.someField`="Jim Morrison"
> -> AND `anotherTable.date`< NOW();
> ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list'
> mysql>
> mysql> SELECT count(someField) FROM userTable;
> +---------------+
> | count(someField) |
> +---------------+
> | 5076 |
> +---------------+
> 1 row in set (0.00 sec)
>
> mysql>
>
> What could be the issue here? Thanks!
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Update on inner join - looks good to me, where did I go wrong?

On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen wrote:
> `userTable.userid` => `userTable`.`userid`
>

Thank you Carsten. That was indeed the problem! Have a peaceful weekend.

--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org