Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

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

Posted on 2011-09-09 22:09:09 by Dotan Cohen

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

Report this message

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

Posted on 2011-09-09 23:01:46 by Dotan Cohen

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

Report this message

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

Posted on 2011-09-10 00:48:27 by Carsten Pedersen

`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

Report this message

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

Posted on 2011-09-10 11:27:23 by Dotan Cohen

On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen <carsten@bitbybit.dk> 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

Report this message