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!

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 to http://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

Captain Paralytic <paul_lautman@yahoo.com> writes:

> 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 to http://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

Can you please recommend a join tutorial. It drives me nuts each and
every time. I'm not sure if I am reading SQL wrong or I am playing dumb,
but I just cant get it.

On 16 Oct, 16:29, Shamrock <shamrock_nos...@null.dev> wrote:
> Captain Paralytic <paul_laut...@yahoo.com> writes:
> > 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
>
> Can you please recommend a join tutorial. It drives me nuts each and
> every time. I'm not sure if I am reading SQL wrong or I am playing dumb,
> but I just cant get it.- Hide quoted text -
>
> - Show quoted text -

Hmmm, having come from an ISAM/VSAM background, I have a picture in my
head of the mechanics of JOINs that mean that I instinctively
understood what they were doing. So I never really read any tutorials.

I put
mysql join tutorial
into Google and took a look at some of them there for you. in truth
the mysql keyword wasn't really necessary as once you understand the
concept of how JOINs work, you can use them in any SQL system that
supports them.

http://www.atlasindia.com/sql.htm seemed to have a good explanation of
the concepts.
and
http://www.tizag.com/mysqlTutorial/mysqljoins.php
and
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

both seemed quite good.

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.

On 17 Oct, 00:35, l3vi <ad...@madfrogproductions.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

So you tried to invent your own multi-table syntax by using a table
qualifier on a field name, without naming the table in the UPDATE
clause and then wonder why it doesn't work!

If you want to use a multi-table update you have to follow the syntax,
it's as simple as that.

If you want to limit the updates in some way, how about adding a check
on the "size" of your primary key?