On Oct 16, 11:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 16 Oct, 15:27, l3vi <ad...@madfrogproductions.com> wrote:
>
>
>
> > I have this query I been playing with
>
> > UPDATE keyword_search SET keyword_search.SCount = 1 WHERE
> > ResultC_DataBase.Phrase = keyword_search.Keyword AND
> > ResultC_DataBase.ResultC < 100 AND keyword_search.SCount > 100 LIMIT
> > 50
>
> > Its two tables and only one table (keyword_search) is being updated.
>
> > I have attempted a LEFT JOIN as well as a CROSS JOIN without any
> > luck... What Im shooting to do is cross ref two tables to adjust data
> > on keyword_search then reset the data to 1 or round(SCount*0.001).
>
> > MySql version 5.0.32
>
> > If anyone can , Thanks!
>
> First piece of advice is to set your query out so that you can see
> what is happening where.
> Second piece of avdvice is that, if you have tried to run a query and
> have got an error, post the error or what is happening so that we can
> look at the current situation.
> Third piece of advice is to READ THE F%£$ MANUAL.
>
> UPDATE keyword_search
> SET keyword_search.SCount = 1
> WHERE ResultC_DataBase.Phrase = keyword_search.Keyword
> AND ResultC_DataBase.ResultC < 100 AND keyword_search.SCount > 100
> LIMIT 50
>
> So, the above layout is piece of advice 1.
> For piece of advice 3, we go tohttp://dev.mysql.com/doc/refman/5.0/en/update.html
> What we note is that in the multi table update example:
>
> UPDATE items,month
> SET items.price=month.price
> WHERE items.id=month.id;
>
> both tables need to be referenced in the UPDATE clause
>
> Over to you for piece of advice 2

Yeah I read that before I posted, and if you notice in my query I have
a LIMIT 50, while in the ref docs "For the multiple-table syntax,
UPDATE updates rows in each table named in table_references that
satisfy the conditions. In this case, ORDER BY and LIMIT cannot be
used.". However because I have 500 million rows of data to play with,
I have to limit it unless I like to watch by box grind to a halt.

So that gives us the #1221 - Incorrect usage of UPDATE and LIMIT, and
lacking the 2nd table in the joined update we get an Unknown table
error. So once more, anyone have a way around this problem Im running
into? I just need to update one table but use two tables in the where
clause to acquire what rows to update, and I'm not interested in a two
step query setup.