RE: update query

[Rick James]

How many rows in each table?
If only one row, why is the schema designed that way?
If multiple rows, why are you changing _all_ rows that way?

I am questioning the schema design that would lead to your question.

Follow on to Ananda's answer:  See
   information_schema.TABLES  WHERE TABLE_SCHEMA = 'dbname'
   information_schema.COLUMNS  WHERE COLUMN_NAME = 'client'

> -Original Message-
> From: Ananda Kumar [mailto:anan...@gmail.com]
> Sent: Monday, April 30, 2012 2:26 AM
> To: Pothanaboyina Trimurthy
> Cc: mysql@lists.mysql.com
> Subject: Re: update query
> 
> Do you just want to replace current value in client column to "NEW".
> You can write a stored proc , with a cursor and loop through the
> cursor, update each table.
> 
> regards
> anandkl
> 
> On Mon, Apr 30, 2012 at 2:47 PM, Pothanaboyina Trimurthy <
> skd.trimur...@gmail.com> wrote:
> 
> > Hi all,
> >  i have one database with 120 tables and each table contains one
> > common column that is "client" now i want to update all the tables
> > column client = "NEW". is it possible to write a single query to
> > update this one.
> >
> > please help me.
> >
> > thanks in advance
> >
> > Thanks & Kind Regards,
> > Trimurthy.p
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

回复： update query

[Zhangzhigang]

Ok, there is another approach if you are using shell script.

Step 1: You may invoke one mysql user who has not password to access the mysql 
database.
Step 2: Shell script:

c=0
for i in `mysql -u username -e "use database;show tables;"`
do
    if [ $c -ge 1 ]
    then
    mysql -u username  -e "use database;update $i set client='NEW'"
    fi
    let c=$c+1
done





 发件人： Ananda Kumar 
收件人： Pothanaboyina Trimurthy  
抄送： mysql@lists.mysql.com 
发送日期： 2012年4月30日, 星期一, 下午 5:26
主题: Re: update query
 
Do you just want to replace current value in client column to "NEW".
You can write a stored proc , with a cursor and loop through the cursor,
update each table.

regards
anandkl

On Mon, Apr 30, 2012 at 2:47 PM, Pothanaboyina Trimurthy <
skd.trimur...@gmail.com> wrote:

> Hi all,
>      i have one database with 120 tables and each table contains one
> common column that is "client" now i want to update all the tables
> column client = "NEW". is it possible to write a single query to
> update this one.
>
> please help me.
>
> thanks in advance
>
> Thanks & Kind Regards,
> Trimurthy.p
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

Re: update query

[Ananda Kumar]

Do you just want to replace current value in client column to "NEW".
You can write a stored proc , with a cursor and loop through the cursor,
update each table.

regards
anandkl

On Mon, Apr 30, 2012 at 2:47 PM, Pothanaboyina Trimurthy <
skd.trimur...@gmail.com> wrote:

> Hi all,
>  i have one database with 120 tables and each table contains one
> common column that is "client" now i want to update all the tables
> column client = "NEW". is it possible to write a single query to
> update this one.
>
> please help me.
>
> thanks in advance
>
> Thanks & Kind Regards,
> Trimurthy.p
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

update query

[Pothanaboyina Trimurthy]

Hi all,
  i have one database with 120 tables and each table contains one
common column that is "client" now i want to update all the tables
column client = "NEW". is it possible to write a single query to
update this one.

please help me.

thanks in advance

Thanks & Kind Regards,
Trimurthy.p

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Update query problem

[Travis Ard]

Try using the IS NULL operator instead of !

-Travis

-Original Message-
From: Andy Wallace [mailto:awall...@ihouseweb.com] 
Sent: Thursday, September 16, 2010 10:47 AM
To: mysql@lists.mysql.com
Subject: Update query problem

So I'm having a problem with an update query. I have three tables:

Table: A
Columns:   acnt, name, company, email, domain

Table: AM
Columns:   acnt, m_id

Table: M
Columns:   m_id, name, company, email, domain

and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).

This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.


update  A
join   AM on A.acnt = AM.acnt
joinM on AM.m_id = M.m_id
SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name),
 M.company = IF( (!M.company AND A.company != ''), A.company,
M.company),
 M.email   = IF( (!M.email   AND A.email != ''),   A.email,   M.email),
 M.domain  = IF( (!M.domain  AND A.domain != ''),  A.domain,  M.domain)

Any thoughts?

THanks,
andy

-- 
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Update query problem

[Andy Wallace]

So I'm having a problem with an update query. I have three tables:

Table: A
Columns:   acnt, name, company, email, domain

Table: AM
Columns:   acnt, m_id

Table: M
Columns:   m_id, name, company, email, domain

and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).

This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.


update  A
join   AM on A.acnt = AM.acnt
joinM on AM.m_id = M.m_id
SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name),
M.company = IF( (!M.company AND A.company != ''), A.company, M.company),
M.email   = IF( (!M.email   AND A.email != ''),   A.email,   M.email),
M.domain  = IF( (!M.domain  AND A.domain != ''),  A.domain,  M.domain)

Any thoughts?

THanks,
andy

--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Speeding up a pretty simple correlated update query

[Hank]

On Sun, Sep 6, 2009 at 6:01 PM, mos  wrote:

>
>  So I've disabled all the keys on the "item_trans" table -- since I am
>> updating every row, it wouldn't (shouldn't) be using the index anyway.
>>
>
> You can't disable unique indexes or primary keys. They are always active.
> You can only deactivate non-unique indexes.
>
> Here are a couple of suggestions.
>
> For now drop the index on item_seq.seq and desc.seq.
> Are you sure you have a compound index on item_seq.itemid and
> item_seq.category??
>
> do a "Show create table item_seq" and also "Show create table desc" to see
> what you've got.
>
> Make sure your my.cnf file has
> key_buffer_size=500M
>
> equal to about 30% of your available memory. You can always reduce it
> later.
>
> Of course there is another way of doing it, if you are willing to have the
> tail wag the dog. You may kick yourself for not discovering it yourself. :)
>
> set @num:=0;
> set @last:='';
> create table new_item_trans select IF(concat(itemid,category),@last,
> @num:=...@num+1,@num) Seq, itemid, category, transid, ...
> ,@last:=concat(itemid,category) as TMPLast from item_trans order by
> concat(itemid,category);
>
> Now you can use the Alter statement to add your indexes and get rid of the
> TMPLast column.
>
> To build the  item_seq table you would now use:
>
> create table item_seq select seq, itemid, category from new_item_trans
> group by seq, itemid, category;
> And of course build your indexes on seq and rename the new_item_trans.
>
> I guarantee you this last solution will not take 9 days to complete! :-)
>
> Mike
>
>
Hi Mike,

 Thanks for your reply.  First, in my tests, I've created the target table
(item_trans) as a copy of the source table with no indexes at all (even no
primary key). Once I get the item_seq field populated, I'll go back and
re-create the indexes in batch using myisamchk (I've posted about this
recently).

 Second, I like your second creative solution (I never would have come up
with that), but in order for it to work, mysql would have to sort 180
million records before creating the table or retrieve them out of the table
via the contactenated index, both of which I think will take a long time...
but I'll certainly give it a shot tomorrow and let you know how it goes.
Thanks again.

-Hank

Re: Speeding up a pretty simple correlated update query

[mos]

So I've disabled all the keys on the "item_trans" table -- since I am
updating every row, it wouldn't (shouldn't) be using the index anyway.


You can't disable unique indexes or primary keys. They are always active. 
You can only deactivate non-unique indexes.


Here are a couple of suggestions.

For now drop the index on item_seq.seq and desc.seq.
Are you sure you have a compound index on item_seq.itemid and 
item_seq.category??


do a "Show create table item_seq" and also "Show create table desc" to see 
what you've got.


Make sure your my.cnf file has
key_buffer_size=500M

equal to about 30% of your available memory. You can always reduce it later.

Of course there is another way of doing it, if you are willing to have the 
tail wag the dog. You may kick yourself for not discovering it yourself. :)


set @num:=0;
set @last:='';
create table new_item_trans select IF(concat(itemid,category),@last, 
@num:=...@num+1,@num) Seq, itemid, category, transid, ... 
,@last:=concat(itemid,category) as TMPLast from item_trans order by 
concat(itemid,category);


Now you can use the Alter statement to add your indexes and get rid of the 
TMPLast column.


To build the  item_seq table you would now use:

create table item_seq select seq, itemid, category from new_item_trans 
group by seq, itemid, category;

And of course build your indexes on seq and rename the new_item_trans.

I guarantee you this last solution will not take 9 days to complete! :-)

Mike

At 12:32 PM 9/6/2009, Hank wrote:

Hello All,
 I'm reposting this since I didn't get much response the last time, so I'm
hoping to reach out again.  My correlated update query (see below) was
running for 9 days before I killed it.   Here is my original question:

  I have a legacy application which was written using a compound primary key
of an item number (non unique) along with a category ID. The combination of
the item number and category ID make the records unique.

  I am in the process of replacing the compound (VARCHAR) keys with an
unique integer key in these tables.

So I have created an item_seq table and assigned a unique sequence number to
each compound key -- it looks like this (all tables are myisam tables, and
mysql version 5.0)

desc item_seq;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| seq   | int(10) unsigned | NO   | PRI | NULL| auto_increment |
| itemid| char(11) | NO   | MUL | ||
| category  | char(4)  | NO   | | ||
+---+--+--+-+-++

I also have my main transactional table with about 180,000,000 rows -- it
looks like this:

desc item_trans;
+-+---+--+-+-+---+
| Field   | Type  | Null | Key | Default |
Extra |
+-+---+--+-+-+---+
| seq | int(10) unsigned  | NO   | MUL | |
|
| itemid  | char(11)  | NO   | PRI | |
|
| category| char(4)   | NO   | PRI | |
|
| transid | int(10)   | NO   | PRI | |
|

Currently the "seq" field is null for the entire table.  So of course, I
want to update the main transaction table with the new sequence number.

So I've disabled all the keys on the "item_trans" table -- since I am
updating every row, it wouldn't (shouldn't) be using the index anyway.

Here is my correlated update query:

 update item_trans i, item_seq is
 set i.seq=is.seq
 where is.itemid=i.itemid and is.category=i.category;

  If I run an explain on the select version of the update, this is what I
get:

++-+--++---++-++---+---+
| id | select_type | table| type   | possible_keys | key| key_len |
ref| rows  | Extra |
++-+--++---++-++---+---+
|  1 | SIMPLE  | item_trans| ALL| PRIMARY   | NULL   | NULL
   | NULL   | 178948797 |   |
|  1 | SIMPLE  | item_seq | eq_ref | itemid| itemid | 20  |
g.item_trans.itemid,g.item_trans.category| 1 |
|
++-+--++---++-++---+---+

... which is exactly what I would expect it to do

Re: Speeding up a pretty simple correlated update query

[Hank]

Hello All,
 I'm reposting this since I didn't get much response the last time, so I'm
hoping to reach out again.  My correlated update query (see below) was
running for 9 days before I killed it.   Here is my original question:

  I have a legacy application which was written using a compound primary key
of an item number (non unique) along with a category ID. The combination of
the item number and category ID make the records unique.

  I am in the process of replacing the compound (VARCHAR) keys with an
unique integer key in these tables.

So I have created an item_seq table and assigned a unique sequence number to
each compound key -- it looks like this (all tables are myisam tables, and
mysql version 5.0)

desc item_seq;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| seq   | int(10) unsigned | NO   | PRI | NULL| auto_increment |
| itemid| char(11) | NO   | MUL | ||
| category  | char(4)  | NO   | | ||
+---+--+--+-+-++

I also have my main transactional table with about 180,000,000 rows -- it
looks like this:

desc item_trans;
+-+---+--+-+-+---+
| Field   | Type  | Null | Key | Default |
Extra |
+-+---+--+-+-+---+
| seq | int(10) unsigned  | NO   | MUL | |
|
| itemid  | char(11)  | NO   | PRI | |
|
| category| char(4)   | NO   | PRI | |
|
| transid | int(10)   | NO   | PRI | |
|

Currently the "seq" field is null for the entire table.  So of course, I
want to update the main transaction table with the new sequence number.

So I've disabled all the keys on the "item_trans" table -- since I am
updating every row, it wouldn't (shouldn't) be using the index anyway.

Here is my correlated update query:

 update item_trans i, item_seq is
 set i.seq=is.seq
 where is.itemid=i.itemid and is.category=i.category;

  If I run an explain on the select version of the update, this is what I
get:

++-+--++---++-++---+---+
| id | select_type | table| type   | possible_keys | key| key_len |
ref| rows  | Extra |
++-+--++---++-++---+---+
|  1 | SIMPLE  | item_trans| ALL| PRIMARY   | NULL   | NULL
   | NULL   | 178948797 |   |
|  1 | SIMPLE  | item_seq | eq_ref | itemid| itemid | 20  |
g.item_trans.itemid,g.item_trans.category| 1 |
|
++-+--++---++-++---+---+

... which is exactly what I would expect it to do.  Update every record of
the item_trans table, and do a full index lookup on the items_seq table.

SO... I've been running this query to update item_trans, and it's been
running for 5 days now.

I've also tried running this with the primary key index on the item_trans
table (but not the seq index), and that ran slower in my initial tests.

Are there any faster ways to update 180 million records with a correlated
update query?  And I'm fairly certain that trying to do this in PHP
one-record at a time would take much longer than a SQL solution.

Thanks,

-Hank

Re: Speeding up a pretty simple correlated update query

[Hank]

Hello Gavin,
 That's what I did with the first one-to-one table to create the unique SEQ
field mapping to each item/category combination.  The problem is on the
TRANSACTION table, where there are multiple instances of each item/category.
 If I just put a auto_increment primary key on that table, I'd get a unique
TRANSACTION ID, which is not what I want.  I want to populate the
transaction table with the new integer seq key created in the first table.

I guess I should have stated that my overall objective here is to eventually
drop the VARCHAR itemid and category id fields from the transaction table,
leaving only the new item sequence id (plus transid) as the primary key.
There are many tables throughout the schema that do this, and I would be
replacing them all.  It's just that this is the largest table, and the
correlated update is taking a long time, and I'm looking for a better
solution (if one exists).  thanks.

-Hank


On Wed, Sep 2, 2009 at 7:50 PM, Gavin Towey  wrote:

> Do you know that if you create seq column on the original table as an
> auto_increment primary key, it will fill in the numbers automatically?
>  There's no need to create the values on another table and update with a
> join.
>
> Regards,
> Gavin Towey
>
> -Original Message-
> From: Hank [mailto:hes...@gmail.com]
> Sent: Wednesday, September 02, 2009 4:35 PM
> To: mysql@lists.mysql.com
> Subject: Speeding up a pretty simple correlated update query
>
> Hello All,
>  I have a legacy application which was written using a compound primary key
> of an item number (non unique) along with a category ID. The combination of
> the item number and category ID make the records unique.
>
>  I am in the process of replacing the compound (VARCHAR) keys with an
> unique integer key in these tables.
>
> So I have created an item_seq table and assigned a unique sequence number
> to
> each compound key -- it looks like this (all tables are myisam tables, and
> mysql version 5.0)
>
> desc item_seq;
> +---+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra  |
> +---+--+--+-+-++
> | seq   | int(10) unsigned | NO   | PRI | NULL| auto_increment |
> | itemid| char(11) | NO   | MUL | ||
> | category  | char(4)  | NO   | | ||
> +---+--+--+-+-++
>
> I also have my main transactional table with about 180,000,000 rows -- it
> looks like this:
>
> desc item_trans;
>
> +-+---+--+-+-+---+
> | Field   | Type  | Null | Key | Default |
> Extra |
>
> +-+---+--+-+-+---+
> | seq | int(10) unsigned  | NO   | MUL | |
>|
> | itemid  | char(11)  | NO   | PRI | |
>|
> | category| char(4)   | NO   | PRI | |
>|
> | transid | int(10)   | NO   | PRI | |
>|
>
> Currently the "seq" field is null for the entire table.  So of course, I
> want to update the main transaction table with the new sequence number.
>
> So I've disabled all the keys on the "item_trans" table -- since I am
> updating every row, it wouldn't (shouldn't) be using the index anyway.
>
> Here is my correlated update query:
>
>  update item_trans i, item_seq is
>  set i.seq=is.seq
>  where is.itemid=i.itemid and is.category=i.category;
>
>  If I run an explain on the select version of the update, this is what I
> get:
>
>
> ++-+--++---++-++---+---+
> | id | select_type | table| type   | possible_keys | key| key_len |
> ref| rows  | Extra |
>
> ++-+--++---++-++---+---+
> |  1 | SIMPLE  | item_trans| ALL| PRIMARY   | NULL   | NULL
>   | NULL   | 178948797 |   |
> |  1 | SIMPLE  | item_seq | eq_ref | itemid| itemid | 20  |
> g.item_trans.itemid,g.item_trans.category| 1 |
> |
>
> ++-+--++---++-++---+---+
>
>

RE: Speeding up a pretty simple correlated update query

[Gavin Towey]

Do you know that if you create seq column on the original table as an 
auto_increment primary key, it will fill in the numbers automatically?  There's 
no need to create the values on another table and update with a join.

Regards,
Gavin Towey

-Original Message-
From: Hank [mailto:hes...@gmail.com]
Sent: Wednesday, September 02, 2009 4:35 PM
To: mysql@lists.mysql.com
Subject: Speeding up a pretty simple correlated update query

Hello All,
  I have a legacy application which was written using a compound primary key
of an item number (non unique) along with a category ID. The combination of
the item number and category ID make the records unique.

  I am in the process of replacing the compound (VARCHAR) keys with an
unique integer key in these tables.

So I have created an item_seq table and assigned a unique sequence number to
each compound key -- it looks like this (all tables are myisam tables, and
mysql version 5.0)

desc item_seq;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| seq   | int(10) unsigned | NO   | PRI | NULL| auto_increment |
| itemid| char(11) | NO   | MUL | ||
| category  | char(4)  | NO   | | ||
+---+--+--+-+-++

I also have my main transactional table with about 180,000,000 rows -- it
looks like this:

desc item_trans;
+-+---+--+-+-+---+
| Field   | Type  | Null | Key | Default |
Extra |
+-+---+--+-+-+---+
| seq | int(10) unsigned  | NO   | MUL | |
|
| itemid  | char(11)  | NO   | PRI | |
|
| category| char(4)   | NO   | PRI | |
|
| transid | int(10)   | NO   | PRI | |
|

Currently the "seq" field is null for the entire table.  So of course, I
want to update the main transaction table with the new sequence number.

So I've disabled all the keys on the "item_trans" table -- since I am
updating every row, it wouldn't (shouldn't) be using the index anyway.

Here is my correlated update query:

 update item_trans i, item_seq is
 set i.seq=is.seq
 where is.itemid=i.itemid and is.category=i.category;

  If I run an explain on the select version of the update, this is what I
get:

++-+--++---++-++---+---+
| id | select_type | table| type   | possible_keys | key| key_len |
ref| rows  | Extra |
++-+--++---++-++---+---+
|  1 | SIMPLE  | item_trans| ALL| PRIMARY   | NULL   | NULL
   | NULL   | 178948797 |   |
|  1 | SIMPLE  | item_seq | eq_ref | itemid| itemid | 20  |
g.item_trans.itemid,g.item_trans.category| 1 |
|
++-+--++---++-++---+---+

... which is exactly what I would expect it to do.  Update every record of
the item_trans table, and do a full index lookup on the items_seq table.

SO... I've been running this query to update item_trans, and it's been
running for 5 days now.

I've also tried running this with the primary key index on the item_trans
table (but not the seq index), and that ran slower in my initial tests.

Are there any faster ways to update 180 million records with a correlated
update query?  And I'm fairly certain that trying to do this in PHP
one-record at a time would take much longer than a SQL solution.

Thanks,

-Hank

The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Speeding up a pretty simple correlated update query

[Hank]

Hello All,
  I have a legacy application which was written using a compound primary key
of an item number (non unique) along with a category ID. The combination of
the item number and category ID make the records unique.

  I am in the process of replacing the compound (VARCHAR) keys with an
unique integer key in these tables.

So I have created an item_seq table and assigned a unique sequence number to
each compound key -- it looks like this (all tables are myisam tables, and
mysql version 5.0)

desc item_seq;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| seq   | int(10) unsigned | NO   | PRI | NULL| auto_increment |
| itemid| char(11) | NO   | MUL | ||
| category  | char(4)  | NO   | | ||
+---+--+--+-+-++

I also have my main transactional table with about 180,000,000 rows -- it
looks like this:

desc item_trans;
+-+---+--+-+-+---+
| Field   | Type  | Null | Key | Default |
Extra |
+-+---+--+-+-+---+
| seq | int(10) unsigned  | NO   | MUL | |
|
| itemid  | char(11)  | NO   | PRI | |
|
| category| char(4)   | NO   | PRI | |
|
| transid | int(10)   | NO   | PRI | |
|

Currently the "seq" field is null for the entire table.  So of course, I
want to update the main transaction table with the new sequence number.

So I've disabled all the keys on the "item_trans" table -- since I am
updating every row, it wouldn't (shouldn't) be using the index anyway.

Here is my correlated update query:

 update item_trans i, item_seq is
 set i.seq=is.seq
 where is.itemid=i.itemid and is.category=i.category;

  If I run an explain on the select version of the update, this is what I
get:

++-+--++---++-++---+---+
| id | select_type | table| type   | possible_keys | key| key_len |
ref| rows  | Extra |
++-+--++---++-++---+---+
|  1 | SIMPLE  | item_trans| ALL| PRIMARY   | NULL   | NULL
   | NULL   | 178948797 |   |
|  1 | SIMPLE  | item_seq | eq_ref | itemid| itemid | 20  |
g.item_trans.itemid,g.item_trans.category| 1 |
|
++-+--++---++-++---+---+

... which is exactly what I would expect it to do.  Update every record of
the item_trans table, and do a full index lookup on the items_seq table.

SO... I've been running this query to update item_trans, and it's been
running for 5 days now.

I've also tried running this with the primary key index on the item_trans
table (but not the seq index), and that ran slower in my initial tests.

Are there any faster ways to update 180 million records with a correlated
update query?  And I'm fairly certain that trying to do this in PHP
one-record at a time would take much longer than a SQL solution.

Thanks,

-Hank

Re: Mysql update query gives error of lock wait timeout

[Krishna Chandra Prajapati]

Increase your Innodb_buffer_pool_size. It will solve your problem.

Thanks,
Krishna

On Sat, Aug 29, 2009 at 10:39 AM, Manasi Save <
manasi.s...@artificialmachines.com> wrote:

> Hi All,
>
> I have a query which gives an error of lock wait timeout only this
> transaction is running with 2 records in the table.
>
> The query is :
>
> Update Test
> Set TestFlag = 1
> Where TestID = 5;
>
> Can this one transaction lock my entire table?
> --
> Thanks and Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
> manasi.s...@artificialmachines.com
> Ph:- 9833537392
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
>
>

Re: Mysql update query gives error of lock wait timeout

[Manasi Save]

Hi,

You are right. The storage engine i m using is InnoDB. but then my concern
is why this simple update query takes so long. Is it true that if its
innodb table then even after specifying where condition it scans all
indexed rows. I am bit confused as mysql.com have mentioned on there site
that even if its a select it locks all the rows and it is reading with
share lock mode.

Can anyone give brief idea about what is exclusive lock and how it works
and what is share lock mode and how it works.

Thanks in advance.

-- 
Regards,
Manasi Save
Artificial Machines Pvt Ltd.

> On Sat, Aug 29, 2009 at 12:09 PM, Manasi Save <
> manasi.s...@artificialmachines.com> wrote:
>
>> Hi All,
>>
>> I have a query which gives an error of lock wait timeout only this
>> transaction is running with 2 records in the table.
>>
>> The query is :
>>
>> Update Test
>> Set TestFlag = 1
>> Where TestID = 5;
>>
>> Can this one transaction lock my entire table?
>>
>>
>>
> Its depend on the storage engine used for the table. For example if the
> table use MyISAM then the transaction will lock entire table, but if you
> use
> InnoDB the transaction will lock only the row which used in transaction.
>
> *cmiiw*
> --
> Muhammad Subair
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Mysql update query gives error of lock wait timeout

[muhammad subair]

On Sat, Aug 29, 2009 at 12:09 PM, Manasi Save <
manasi.s...@artificialmachines.com> wrote:

> Hi All,
>
> I have a query which gives an error of lock wait timeout only this
> transaction is running with 2 records in the table.
>
> The query is :
>
> Update Test
> Set TestFlag = 1
> Where TestID = 5;
>
> Can this one transaction lock my entire table?
>
>
>
Its depend on the storage engine used for the table. For example if the
table use MyISAM then the transaction will lock entire table, but if you use
InnoDB the transaction will lock only the row which used in transaction.

*cmiiw*
-- 
Muhammad Subair

Mysql update query gives error of lock wait timeout

[Manasi Save]

Hi All,

I have a query which gives an error of lock wait timeout only this
transaction is running with 2 records in the table.

The query is :

Update Test
Set TestFlag = 1
Where TestID = 5;

Can this one transaction lock my entire table?
-- 
Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.
manasi.s...@artificialmachines.com
Ph:- 9833537392




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Error 1064 on a basic UPDATE query

[Baron Schwartz]

Hi,

James Graham wrote:

Hi List,

We have a transactions table (~600k records). I'm trying a simple update
query, it fails.
I thought this could be due to a reserved word problem, but all names
have been escaped.

mysql> UPDATE `tblTransaction` SET `TYPE` = 'Manual' WHERE (`ORDERID` =
'694215576aac60f10c9eebe4a5a39d0f');
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 'UPDATE `tblTransaction` SET `TYPE` = 'Manual' WHERE
(`ORDERID` = '694215576aac60' at line 1

If anybody has any ideas, please let me know :)


Not a clue.  Is there a funny character that only looks like a space, or 
something of that nature?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Error 1064 on a basic UPDATE query

[James Graham]

Hi List,

We have a transactions table (~600k records). I'm trying a simple update
query, it fails.
I thought this could be due to a reserved word problem, but all names
have been escaped.

mysql> UPDATE `tblTransaction` SET `TYPE` = 'Manual' WHERE (`ORDERID` =
'694215576aac60f10c9eebe4a5a39d0f');
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 'UPDATE `tblTransaction` SET `TYPE` = 'Manual' WHERE
(`ORDERID` = '694215576aac60' at line 1

If anybody has any ideas, please let me know :)

Thanks, James

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query question

[ViSolve DB Team]

Hi,,

The Update query of yours will do fine..
otherwise try using string functions [instr()] like

mysql> update inventory_items set name='necklace' where 
instr(description,'necklace')>0;


Thanks
ViSolve DB Team.
- Original Message - 
From: "Jerry Jones" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, February 03, 2007 8:42 AM
Subject: Update query question



I am new to mysql. I am trying to do a simple update query to update a
field based on the contents of another field in the same table.
Here is what I have.
update inventory_items set name = "necklace" where description like
"%necklace%";
I am not sure what is wrong. select * from inventory_items where
description like "%necklace%"; works just fine. I cannot find much
online to help me out with this.
To summarize, I need to update the name field to "necklace" when the
word necklace shows up anywhere in the description field.
Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update query question

[Jerry Jones]

I am new to mysql. I am trying to do a simple update query to update a
field based on the contents of another field in the same table.
Here is what I have.
update inventory_items set name = "necklace" where description like
"%necklace%";
I am not sure what is wrong. select * from inventory_items where
description like "%necklace%"; works just fine. I cannot find much
online to help me out with this.
To summarize, I need to update the name field to "necklace" when the
word necklace shows up anywhere in the description field.
Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query help

[Remo Tex]

Ravi Kumar. wrote:

Dear Friends,
 
I have two tables: T1, T2.
 
T1 has 3 columns: playerid, gameid, score

T2 has 2 columns: playerid, totalscore.
 
I wish to update table T2 such that sum of T1.score of each player, gets

updated in T2.totalscore. It may be something like this:
 
update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
T2.playerid 
 
OR
 
update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =

T2.playerid group by playerid
 
However none of the above is working.
 
Where am I wrong? Please help.
 
The version of MySQL I am using is 4.1.14-standard-log.
 
Thanks,
 
Ravi.
 



First I assunme you've done
INSERT INTO T2 SELECT DISTINCT(playerid), NULL FROM T1;

Since you need an aggregate function like SUM() and it needs GROUP BY 
alas manual says "For the multiple-table syntax, UPDATE ... In this 
case, ORDER BY and LIMIT cannot be used.":

http://dev.mysql.com/doc/refman/4.1/en/update.html

so I suppose you could try s.th. like this:
1. In code update each T2.totalscore in separate query using

SELECT SUM(score) as totalscore FROM T1 GROUP BY playerid;
Traverse resultset and in code (perl/php/whatever) update each 
T2.totalscore with result




2. OR you can try second approach

UPDATE T2 SET totalscore=0

then you could try this
UPDATE T2 JOIN T1 ON T2.playerid=T1.playerid
SET T2.totalscore = T2.totalscore + COALESCE(T1.score, 0);

3. If your version supports subqueries... well then it is easy ;-)
If you want to update a table based on an aggregate function applied to 
another table, you can use a correlated subquery, for example:


UPDATE T2
SET totalscore =
 (SELECT SUM(T1.score) FROM T1 WHERE T2.playerid=T1.playerid)

Additional information on MySQL correlated subqueries is at 
http://dev.mysql.com/doc/mysql/en/correlated-subqueries.html

HTH :-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query help

[Remo Tex]

ViSolve DB Team wrote:

Hi,

Try this..

UPDATE table2 inner join table1 on table2.playedid=table1.playerid
SET table2.totalscore=sum(table1.score)

Just a guess...

Thanks,
ViSolve DB Team

- Original Message - From: "Ravi Kumar." <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, December 06, 2006 4:11 PM
Subject: Update query help



Dear Friends,

I have two tables: T1, T2.

T1 has 3 columns: playerid, gameid, score
T2 has 2 columns: playerid, totalscore.

I wish to update table T2 such that sum of T1.score of each player, gets
updated in T2.totalscore. It may be something like this:

update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
T2.playerid

OR

update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
T2.playerid group by playerid

However none of the above is working.

Where am I wrong? Please help.

The version of MySQL I am using is 4.1.14-standard-log.

Thanks,

Ravi.





 




No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006



ViSolve, I think yo've missed a GROUP BY needed for every Aggregated 
function (like SUM) ;-)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query help

[ViSolve DB Team]

Hi,

Try this..

UPDATE table2 inner join table1 on table2.playedid=table1.playerid
SET table2.totalscore=sum(table1.score)

Just a guess...

Thanks,
ViSolve DB Team

- Original Message - 
From: "Ravi Kumar." <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, December 06, 2006 4:11 PM
Subject: Update query help



Dear Friends,

I have two tables: T1, T2.

T1 has 3 columns: playerid, gameid, score
T2 has 2 columns: playerid, totalscore.

I wish to update table T2 such that sum of T1.score of each player, gets
updated in T2.totalscore. It may be something like this:

update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
T2.playerid

OR

update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
T2.playerid group by playerid

However none of the above is working.

Where am I wrong? Please help.

The version of MySQL I am using is 4.1.14-standard-log.

Thanks,

Ravi.








No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update query help

[Ravi Kumar.]

Dear Friends,
 
I have two tables: T1, T2.
 
T1 has 3 columns: playerid, gameid, score
T2 has 2 columns: playerid, totalscore.
 
I wish to update table T2 such that sum of T1.score of each player, gets
updated in T2.totalscore. It may be something like this:
 
update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
T2.playerid 
 
OR
 
update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid =
T2.playerid group by playerid
 
However none of the above is working.
 
Where am I wrong? Please help.
 
The version of MySQL I am using is 4.1.14-standard-log.
 
Thanks,
 
Ravi.
 

RE: Update query in order to modify some fields

[Jerry Schwartz]

Wouldn't that would also change "theater" to "anaater"? You need the
hyphens:

mysql> SELECT REPLACE("theater", "the-", "an-");
+---+
| REPLACE("theater", "the-", "an-") |
+---+
| theater   |
+---+
1 row in set (0.06 sec)

mysql> SELECT REPLACE("the-theater", "the-", "an-");
+---+
| REPLACE("the-theater", "the-", "an-") |
+---+
| an-theater|
+---+
1 row in set (0.03 sec)

You will also have problems if there are capitalization differences.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: spacemarc [mailto:[EMAIL PROTECTED]
> Sent: Friday, November 17, 2006 9:27 AM
> To: Mike Kruckenberg
> Cc: mysql@lists.mysql.com
> Subject: Re: Update query in order to modify some fields
>
> 2006/11/17, Mike Kruckenberg <[EMAIL PROTECTED]>:
> > If it's values you are updating you can use the replace() string
> > function to do something like this:
> >
> > update table1 set field1=replace(field1,'the','an');
> >
> > To demonstrate:
> >
> > mysql> select replace("the-object1","the","an");
> > +---+
> > | replace("the-object1","the","an") |
> > +---+
> > | an-object1|
> > +---+
> > 1 row in set (0.09 sec)
> >
>
> ok.
> I will use the replace function in Update query.
>
>
> --
> http://www.spacemarc.it
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query in order to modify some fields

[spacemarc]

2006/11/17, Mike Kruckenberg <[EMAIL PROTECTED]>:

If it's values you are updating you can use the replace() string
function to do something like this:

update table1 set field1=replace(field1,'the','an');

To demonstrate:

mysql> select replace("the-object1","the","an");
+---+
| replace("the-object1","the","an") |
+---+
| an-object1|
+-------+
1 row in set (0.09 sec)



ok.
I will use the replace function in Update query.


--
http://www.spacemarc.it

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update query in order to modify some fields

[spacemarc]

Hi
I have more fields with values as

the-object1
the-object2
the-object45


I must to change only the part begins them to other words, example:

"the-object2" must become "an-object2"

Which syntax I can use?

Thanks in advance
--
http://www.spacemarc.it

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with update query

[Ferindo Middleton]

I agree. I should check for empty strings intead of nulls. The application
doesn't convert them to null and the default value when a user leaves the
field blank on the web page is to save it as an empty string. Thanks.

Ferindo

On 10/16/06, Jerry Schwartz <[EMAIL PROTECTED]> wrote:


You might want to check for an empty string ("") rather than null. From
what
I can tell, HTML forms don't give you NULL values if you leave fields
empty,
they return "". Unless your programs internally convert empty strings to
NULL, you won't find NULL in your table.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: Ferindo Middleton [mailto:[EMAIL PROTECTED]
> Sent: Saturday, October 14, 2006 9:16 PM
> To: Dan Buettner
> Cc: mysql
> Subject: Re: help with update query
>
> Thanks Dan. This does help.  This a pretty straight-forward
> idea. I could
> even save the results of this query to a text file and
> possibly review it a
> little before running it so I don't acidentally do anything
> funky and I
> could see the impact this would have on the data before
> applying it. I think
> maybe I'll even add a "WHERE email_address IS NULL" line
> within the UPDATE
> concatenation so I don't overwrite any records that already have an
> email_address. I'll try this. Thanks alot!
>
> Ferindo
>
> On 10/14/06, Dan Buettner <[EMAIL PROTECTED]> wrote:
> >
> > Ferindo, I had a similar task recently, and the problem you'll run
> > into is that you can't select from and update the same
> table at once.
> > What I ended up doing was doing a SELECT to build the update queries
> > for me.
> >
> > Something like this:
> > SELECT CONCAT(
> > "UPDATE bowler_score SET email_address = '", email_address, "' ",
> > "WHERE firstname = '", firstname, "' ",
> > "AND middlename = '", middlename, "' ",
> > "AND lastname = '", lastname, "' ",
> > "AND race = '", race, "' ",
> > "AND religion = '", religion, "'; " )
> > FROM bowler_score
> > WHERE email_address LIKE "[EMAIL PROTECTED]"
> >
> > This finds all the entries where there appears to be a valid email
> > address (contains @), and updates all the other records for that
> > individual.
> >
> > Note this is not very efficient, since a LOT of update
> queries will be
> > generated, and also that if one person has more than one
> email address
> > (a typo perhaps) you will lose all but one address for them.  But it
> > should work, and it's pretty easy.
> >
> > HTH,
> > Dan
> >
> > On 10/13/06, Ferindo Middleton < [EMAIL PROTECTED]> wrote:
> > > I have a table, bowler_score_records, with the following
> columns:  id,
> > > firstname,  middlename, lastname, race, religion, email_address,
> > > bowling_score, gamedate
> > >
> > > As records get entered to this table, sometimes the users
> forget to
> > input
> > > the email_address but the users always capture the full
> name, race, and
> > > religion. Assuming that no two individuals (bowlers)
> would happen to
> > have
> > > the same name, race, and religion.
> > >
> > > I need to write a query to update the email_address for
> all the records
> > > where the users forgot to input it based on the idea that records
> > carrying
> > > the same full name, race, and religion are in fact the
> same person,
> > hence
> > > the same email_address.
> > >
> > > Based on the schema described above, how would you write it?
> > >
> > > --
> > > Ferindo
> > >
> > >
> >
>
>
>
> --
> Ferindo Middleton
> Web Application Developer/Database Administrator/IT Infrastructure and
> Integration Management Specialist/Perception Augmentation and Control
> Supplementation Research Specialist for AI
> Wetware-to-Software Interface and
> Design
> -Sleekcollar-
>







--
Ferindo Middleton
Web Application Developer/Database Administrator/IT Infrastructure and
Integration Management Specialist/Perception Augmentation and Control
Supplementation Research Specialist for AI Wetware-to-Software Interface and
Design
-Sleekcollar-

RE: help with update query

[Jerry Schwartz]

You might want to check for an empty string ("") rather than null. From what
I can tell, HTML forms don't give you NULL values if you leave fields empty,
they return "". Unless your programs internally convert empty strings to
NULL, you won't find NULL in your table.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: Ferindo Middleton [mailto:[EMAIL PROTECTED]
> Sent: Saturday, October 14, 2006 9:16 PM
> To: Dan Buettner
> Cc: mysql
> Subject: Re: help with update query
>
> Thanks Dan. This does help.  This a pretty straight-forward
> idea. I could
> even save the results of this query to a text file and
> possibly review it a
> little before running it so I don't acidentally do anything
> funky and I
> could see the impact this would have on the data before
> applying it. I think
> maybe I'll even add a "WHERE email_address IS NULL" line
> within the UPDATE
> concatenation so I don't overwrite any records that already have an
> email_address. I'll try this. Thanks alot!
>
> Ferindo
>
> On 10/14/06, Dan Buettner <[EMAIL PROTECTED]> wrote:
> >
> > Ferindo, I had a similar task recently, and the problem you'll run
> > into is that you can't select from and update the same
> table at once.
> > What I ended up doing was doing a SELECT to build the update queries
> > for me.
> >
> > Something like this:
> > SELECT CONCAT(
> > "UPDATE bowler_score SET email_address = '", email_address, "' ",
> > "WHERE firstname = '", firstname, "' ",
> > "AND middlename = '", middlename, "' ",
> > "AND lastname = '", lastname, "' ",
> > "AND race = '", race, "' ",
> > "AND religion = '", religion, "'; " )
> > FROM bowler_score
> > WHERE email_address LIKE "[EMAIL PROTECTED]"
> >
> > This finds all the entries where there appears to be a valid email
> > address (contains @), and updates all the other records for that
> > individual.
> >
> > Note this is not very efficient, since a LOT of update
> queries will be
> > generated, and also that if one person has more than one
> email address
> > (a typo perhaps) you will lose all but one address for them.  But it
> > should work, and it's pretty easy.
> >
> > HTH,
> > Dan
> >
> > On 10/13/06, Ferindo Middleton < [EMAIL PROTECTED]> wrote:
> > > I have a table, bowler_score_records, with the following
> columns:  id,
> > > firstname,  middlename, lastname, race, religion, email_address,
> > > bowling_score, gamedate
> > >
> > > As records get entered to this table, sometimes the users
> forget to
> > input
> > > the email_address but the users always capture the full
> name, race, and
> > > religion. Assuming that no two individuals (bowlers)
> would happen to
> > have
> > > the same name, race, and religion.
> > >
> > > I need to write a query to update the email_address for
> all the records
> > > where the users forgot to input it based on the idea that records
> > carrying
> > > the same full name, race, and religion are in fact the
> same person,
> > hence
> > > the same email_address.
> > >
> > > Based on the schema described above, how would you write it?
> > >
> > > --
> > > Ferindo
> > >
> > >
> >
>
>
>
> --
> Ferindo Middleton
> Web Application Developer/Database Administrator/IT Infrastructure and
> Integration Management Specialist/Perception Augmentation and Control
> Supplementation Research Specialist for AI
> Wetware-to-Software Interface and
> Design
> -Sleekcollar-
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Re: help with update query

[Dan Buettner]

Good call on the WHERE email_address IS NULL thing.  Also occurs to me
you could do a SELECT DISTINCT instead of just a SELECT to eliminate
duplicate update commands.

Glad this was useful.

Dan


On 10/14/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote:

Thanks Dan. This does help.  This a pretty straight-forward idea. I could
even save the results of this query to a text file and possibly review it a
little before running it so I don't acidentally do anything funky and I
could see the impact this would have on the data before applying it. I think
maybe I'll even add a "WHERE email_address IS NULL" line within the UPDATE
concatenation so I don't overwrite any records that already have an
email_address. I'll try this. Thanks alot!

Ferindo

On 10/14/06, Dan Buettner < [EMAIL PROTECTED]> wrote:
> Ferindo, I had a similar task recently, and the problem you'll run
> into is that you can't select from and update the same table at once.
> What I ended up doing was doing a SELECT to build the update queries
> for me.
>
> Something like this:
> SELECT CONCAT(
> "UPDATE bowler_score SET email_address = '", email_address, "' ",
> "WHERE firstname = '", firstname, "' ",
> "AND middlename = '", middlename, "' ",
> "AND lastname = '", lastname, "' ",
> "AND race = '", race, "' ",
> "AND religion = '", religion, "'; " )
> FROM bowler_score
> WHERE email_address LIKE "[EMAIL PROTECTED]"
>
> This finds all the entries where there appears to be a valid email
> address (contains @), and updates all the other records for that
> individual.
>
> Note this is not very efficient, since a LOT of update queries will be
> generated, and also that if one person has more than one email address
> (a typo perhaps) you will lose all but one address for them.  But it
> should work, and it's pretty easy.
>
> HTH,
> Dan
>
> On 10/13/06, Ferindo Middleton < [EMAIL PROTECTED]> wrote:
> > I have a table, bowler_score_records, with the following columns:  id,
> > firstname,  middlename, lastname, race, religion, email_address,
> > bowling_score, gamedate
> >
> > As records get entered to this table, sometimes the users forget to
input
> > the email_address but the users always capture the full name, race, and
> > religion. Assuming that no two individuals (bowlers) would happen to
have
> > the same name, race, and religion.
> >
> > I need to write a query to update the email_address for all the records
> > where the users forgot to input it based on the idea that records
carrying
> > the same full name, race, and religion are in fact the same person,
hence
> > the same email_address.
> >
> > Based on the schema described above, how would you write it?
> >
> > --
> > Ferindo
> >
> >
>



--
Ferindo Middleton
Web Application Developer/Database Administrator/IT Infrastructure and
Integration Management Specialist/Perception Augmentation and Control
Supplementation Research Specialist for AI Wetware-to-Software Interface and
Design
-Sleekcollar-


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with update query

[Ferindo Middleton]

Thanks Dan. This does help.  This a pretty straight-forward idea. I could
even save the results of this query to a text file and possibly review it a
little before running it so I don't acidentally do anything funky and I
could see the impact this would have on the data before applying it. I think
maybe I'll even add a "WHERE email_address IS NULL" line within the UPDATE
concatenation so I don't overwrite any records that already have an
email_address. I'll try this. Thanks alot!

Ferindo

On 10/14/06, Dan Buettner <[EMAIL PROTECTED]> wrote:


Ferindo, I had a similar task recently, and the problem you'll run
into is that you can't select from and update the same table at once.
What I ended up doing was doing a SELECT to build the update queries
for me.

Something like this:
SELECT CONCAT(
"UPDATE bowler_score SET email_address = '", email_address, "' ",
"WHERE firstname = '", firstname, "' ",
"AND middlename = '", middlename, "' ",
"AND lastname = '", lastname, "' ",
"AND race = '", race, "' ",
"AND religion = '", religion, "'; " )
FROM bowler_score
WHERE email_address LIKE "[EMAIL PROTECTED]"

This finds all the entries where there appears to be a valid email
address (contains @), and updates all the other records for that
individual.

Note this is not very efficient, since a LOT of update queries will be
generated, and also that if one person has more than one email address
(a typo perhaps) you will lose all but one address for them.  But it
should work, and it's pretty easy.

HTH,
Dan

On 10/13/06, Ferindo Middleton < [EMAIL PROTECTED]> wrote:
> I have a table, bowler_score_records, with the following columns:  id,
> firstname,  middlename, lastname, race, religion, email_address,
> bowling_score, gamedate
>
> As records get entered to this table, sometimes the users forget to
input
> the email_address but the users always capture the full name, race, and
> religion. Assuming that no two individuals (bowlers) would happen to
have
> the same name, race, and religion.
>
> I need to write a query to update the email_address for all the records
> where the users forgot to input it based on the idea that records
carrying
> the same full name, race, and religion are in fact the same person,
hence
> the same email_address.
>
> Based on the schema described above, how would you write it?
>
> --
> Ferindo
>
>





--
Ferindo Middleton
Web Application Developer/Database Administrator/IT Infrastructure and
Integration Management Specialist/Perception Augmentation and Control
Supplementation Research Specialist for AI Wetware-to-Software Interface and
Design
-Sleekcollar-

Re: help with update query

[Dan Buettner]

Ferindo, I had a similar task recently, and the problem you'll run
into is that you can't select from and update the same table at once.
What I ended up doing was doing a SELECT to build the update queries
for me.

Something like this:
SELECT CONCAT(
"UPDATE bowler_score SET email_address = '", email_address, "' ",
"WHERE firstname = '", firstname, "' ",
"AND middlename = '", middlename, "' ",
"AND lastname = '", lastname, "' ",
"AND race = '", race, "' ",
"AND religion = '", religion, "'; " )
FROM bowler_score
WHERE email_address LIKE "[EMAIL PROTECTED]"

This finds all the entries where there appears to be a valid email
address (contains @), and updates all the other records for that
individual.

Note this is not very efficient, since a LOT of update queries will be
generated, and also that if one person has more than one email address
(a typo perhaps) you will lose all but one address for them.  But it
should work, and it's pretty easy.

HTH,
Dan

On 10/13/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote:

I have a table, bowler_score_records, with the following columns:  id,
firstname,  middlename, lastname, race, religion, email_address,
bowling_score, gamedate

As records get entered to this table, sometimes the users forget to input
the email_address but the users always capture the full name, race, and
religion. Assuming that no two individuals (bowlers) would happen to have
the same name, race, and religion.

I need to write a query to update the email_address for all the records
where the users forgot to input it based on the idea that records carrying
the same full name, race, and religion are in fact the same person, hence
the same email_address.

Based on the schema described above, how would you write it?

--
Ferindo




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

help with update query

[Ferindo Middleton]

I have a table, bowler_score_records, with the following columns:  id,
firstname,  middlename, lastname, race, religion, email_address,
bowling_score, gamedate

As records get entered to this table, sometimes the users forget to input
the email_address but the users always capture the full name, race, and
religion. Assuming that no two individuals (bowlers) would happen to have
the same name, race, and religion.

I need to write a query to update the email_address for all the records
where the users forgot to input it based on the idea that records carrying
the same full name, race, and religion are in fact the same person, hence
the same email_address.

Based on the schema described above, how would you write it?

--
Ferindo

Re: Need Update Query Help (Urgent)

[Peter Brawley]

I wrote this below Query but it shows error how to write



UPDATE table_1
SET col_3 = 'bbb'
WHERE  col_1 = (SELECT max(col_1) FROM table_1)


See the docs for Update at 
http://dev.mysql.com/doc/refman/5.0/en/update.html. You cannot refer to 
the update table in a subquery.


PB

-

Veerabhadrarao Narra wrote:

Hi

  i ahve one table table_1 and columns like col_1,col_2,col_3

col_1   col_2  col_3
1   aa aaa
2   bb

  Now i want to update my table table_1 SET col_3 as bbb where max of col_1

I wrote this below Query but it shows error how to write

UPDATE table_1
SET col_3 = 'bbb'
WHERE  col_1 = (SELECT max(col_1) FROM table_1)
  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need Update Query Help (Urgent)

[Jeff Shapiro]

On Monday 20 February 2006 03:27, Veerabhadrarao Narra wrote:
> Hi
>
>   i ahve one table table_1 and columns like col_1,col_2,col_3
>
> col_1   col_2  col_3
> 1   aa aaa
> 2   bb
>
>   Now i want to update my table table_1 SET col_3 as bbb where max of col_1
>
> I wrote this below Query but it shows error how to write
>
> UPDATE table_1
> SET col_3 = 'bbb'
> WHERE  col_1 = (SELECT max(col_1) FROM table_1)

It appears that you can't do what you want. 

This is at the bottom of the UPDATE syntax page:
" Currently, you cannot update a table and select from the same table in a 
subquery."




However, you can to something like:

select @maximum_column :=max(col_1) from table_1;
UPDATE table_1
SET col_3 = 'bbb'
WHERE  col_1 = @maximum_column;

Perhaps someone else has a better solution.

-- 
Jeff Shapiro
listserv only address

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need Update Query Help (Urgent)

[Veerabhadrarao Narra]

Hi

  i ahve one table table_1 and columns like col_1,col_2,col_3

col_1   col_2  col_3
1   aa aaa
2   bb

  Now i want to update my table table_1 SET col_3 as bbb where max of col_1

I wrote this below Query but it shows error how to write

UPDATE table_1
SET col_3 = 'bbb'
WHERE  col_1 = (SELECT max(col_1) FROM table_1)
-- 
Thanks & Regards,
veerabhadrarao narra,
+91-988-556-5556




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query

[Jørn Dahl-Stamnes]

On Tuesday 24 January 2006 12:03, Gleb Paharenko wrote:
> Hello.
>
> If dbA.id has the format you have specified MySQL should be able to
> silently convert the type from char to int, and you can work with dbA.id
> as it is integer column.
>
>
> mysql> create table ch(id char(6));
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> insert into ch set id='001234';
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select id+0 from ch;
> +--+
>
> | id+0 |
>
> +--+
>
> | 1234 |
>
> +--+
>
> Use something similar to:
>  update dbB, dbA set dbB.foo=dbA.foo, dbB.bar=dbA.bar where
>  dbB.id=dbA.id ;
>
> See:
>   http://dev.mysql.com/doc/refman/5.0/en/update.html

Thanks a lot. That did the trick. I ended up with a command like this:

update newdb.table as T,olddb.table as S set T.foo=S.foo,T.bar=S.bar,...(more 
fields that should be copied) where T.id=S.id;

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query

[Gleb Paharenko]

Hello.

If dbA.id has the format you have specified MySQL should be able to
silently convert the type from char to int, and you can work with dbA.id
as it is integer column.


mysql> create table ch(id char(6));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into ch set id='001234';
Query OK, 1 row affected (0.00 sec)

mysql> select id+0 from ch;
+--+
| id+0 |
+--+
| 1234 |
+--+

Use something similar to:
 update dbB, dbA set dbB.foo=dbA.foo, dbB.bar=dbA.bar where
 dbB.id=dbA.id ;

See:
  http://dev.mysql.com/doc/refman/5.0/en/update.html



Jørn Dahl-Stamnes wrote:
> Assume that you have two tables (in two different databases):
> 
> table A in database dbA:
> 
> idCHAR(6)
> foo   int
> bar   int
> 
> table B in database dbB:
> 
> idINT(6)
> foo   int
> bar   int
> 
> Both tables has a several records with identical ID values, but the format is 
> different ('001234' vs 1234).
> 
> Is it possible to create a update query that copies the 'foo' and 'bar' from 
> table dbA.A to dbB.B for each record in dbB.B?
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update query

[Jørn Dahl-Stamnes]

Assume that you have two tables (in two different databases):

table A in database dbA:

id  CHAR(6)
foo int
bar int

table B in database dbB:

id  INT(6)
foo int
bar int

Both tables has a several records with identical ID values, but the format is 
different ('001234' vs 1234).

Is it possible to create a update query that copies the 'foo' and 'bar' from 
table dbA.A to dbB.B for each record in dbB.B?

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update query to set random data

[zzapper]

Hi,
In order to generate some useful test data

eg randomnly setting the flag select_normal to 0 or 1 use the following

update tbl_products set select_normal=round(rand());

I've actually answered my own question (as usual in formulating the question 
you often can workout
the answer)

But anyone got any other useful queries in this line?

-- 
zzapper
vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?"
http://www.rayninfo.co.uk/tips/ vim, zsh & success tips


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect in an Update query

[Jochem van Dieten]

On 6/17/05, [EMAIL PROTECTED] wrote:
> 
> There is one caveat: It is not currently possible to modify a table and select
> from the same table in a subquery.

That is not the only problem: there is no guarantee the subquery will
only return one record. So even if MySQL wouldn't have this limitation
you would still run the risk of an executor error when the subquery
returns more then one record.

Try this:
UPDATE table1 a, table2 b
SET a.field1 = b.field1
WHERE b.field2 = 'Some Value'
AND a.field2  = 'Another Value'

Jochem

PS Please use single quotes to delimit strings, sticking to the SQL
standard makes it easier to read.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect in an Update query

[Ed Reed]

Thanks for the reply.
 
What do you mean by 'self join'?
 


>>> Eric Bergen <[EMAIL PROTECTED]> 6/17/05 4:39:28 PM >>>
Could you accomplish this with an update and self join?

Ed Reed wrote:

>Sorry, I thought it was easy to understand.
> 
>I wanna update a field in a table with a value from the same field but from a 
>different record of the same table. 
>For example, using the query in my original message,
> 
>+--+
>|RecID |FIELD1 | FIELD2 |
>+--+
>| 1 | ABC | A Value |
>+--+
>| 2 | DEF | Some Value |
>+--+
>| 3 | GHI | Another Value |
>+--+
>| 4 | JKL | More Values |
>+--+
> 
>
>
>
> 
>
>>Update table1 Set field1=(Select field1 From table1 Where field2="Some Value")
>>Where field2 ="Another Value";
>> 
>>
>
>This query should set FIELD1 of Record 3 to 'DEF'
> 
>+--+
>|RecID |FIELD1 | FIELD2 |
>+--+
>| 1 | ABC | A Value |
>+--+
>| 2 | DEF | Some Value |
>+--+
>| 3 | DEF | Another Value |
>+--+
>| 4 | JKL | More Values |
>+--+
>
>
>
>
> 
>That's it! Should be easy but I get an error that says "You can't specify 
>target table 'table1' for update in FROM clause"
> 
>- Thanks
>
>
> 
>
< [EMAIL PROTECTED] > 6/17/05 11:03:40 AM >>>
 

>"Ed Reed" < [EMAIL PROTECTED] > wrote on 06/17/2005 01:35:40 PM:
>
> 
>
>>Can anyone tell me how I can make this work or suggest a work around?
>> 
>>
>
> 
>
>>Update table1 Set field1=(Select field1 From table1 Where field2="Some 
>> 
>>
>Value")
> 
>
>>Where field2 ="Another Value";
>> 
>>
>
> 
>
>>Thanks
>> 
>>
>
>OK, your example must be broken (or it wouldn't be here) and since that's 
>all you posted I have a hard time divining your intent. Would you please 
>provide some sample data and explain what it is you want to do _in words_? 
>I can't speak for everyone but I know it would sure help me figure out 
>what you want to do.
>
>Thanks!
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>
> 
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql 
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 

Re: Subselect in an Update query

[Ed Reed]

Thanks

>>> <[EMAIL PROTECTED]> 6/17/05 2:03:02 PM >>>
Hi,
There is one caveat: It is not currently possible to modify a table and select
from the same table in a subquery.

this phrase is from
http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html 

solution
*
create table t as Select field1 From table1 Where field2="Some
Value"

Update table1 Set field1=(Select field1 From t Where field2="Some
Value")
Where field2 ="Another Value";

**
mysql> select * from upd;
+--+--+
| a | b |
+--+--+
| 1 | one |
| 1 | two |
| 2 | one |
| 2 | two |
| 1 | Un |
+--+--+
5 rows in set (0.09 sec)

mysql> create table t as select * from upd where b='Un';
mysql> update upd set a=(select a from t where b='Un') where b='one';
mysql> select * from upd;
+--+--+
| a | b |
+--+--+
| 1 | one |
| 1 | two |
| 1 | one |< changed
| 2 | two |
| 1 | Un |
+--+--+
5 rows in set (0.00 sec)



Mathias



Selon Ed Reed < [EMAIL PROTECTED] >:

> Can anyone tell me how I can make this work or suggest a work around?
>
> Update table1 Set field1=(Select field1 From table1 Where field2="Some
> Value")
> Where field2 ="Another Value";
>
> Thanks
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql 
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] 

Re: Subselect in an Update query

[Eric Bergen]

Could you accomplish this with an update and self join?

Ed Reed wrote:


Sorry, I thought it was easy to understand.

I wanna update a field in a table with a value from the same field but from a different record of the same table. 
For example, using the query in my original message,


+--+
|RecID |FIELD1 |FIELD2 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | GHI   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+




 


Update table1 Set field1=(Select field1 From table1 Where field2="Some Value")
Where field2 ="Another Value";
   



This query should set FIELD1 of Record 3 to 'DEF'

+--+
|RecID |FIELD1 |FIELD2 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | DEF   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+





That's it!  Should be easy but I get an error that says "You can't specify target 
table 'table1' for update in FROM clause"

- Thanks


 


<[EMAIL PROTECTED]> 6/17/05 11:03:40 AM >>>
   


"Ed Reed" < [EMAIL PROTECTED] > wrote on 06/17/2005 01:35:40 PM:

 


Can anyone tell me how I can make this work or suggest a work around?
   



 

Update table1 Set field1=(Select field1 From table1 Where field2="Some 
   


Value")
 


Where field2 ="Another Value";
   



 


Thanks
   



OK, your example must be broken (or it wouldn't be here) and since that's 
all you posted I have a hard time divining your intent. Would you please 
provide some sample data and explain what it is you want to do _in words_? 
I can't speak for everyone but I know it would sure help me figure out 
what you want to do.


Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect in an Update query

[Ed Reed]

Sorry, I thought it was easy to understand.
 
I wanna update a field in a table with a value from the same field but from a 
different record of the same table. 
For example, using the query in my original message,
 
+--+
|RecID |FIELD1 |FIELD2 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | GHI   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+
 



> Update table1 Set field1=(Select field1 From table1 Where field2="Some Value")
> Where field2 ="Another Value";

This query should set FIELD1 of Record 3 to 'DEF'
 
+--+
|RecID |FIELD1 |FIELD2 |
+--+
|  1   | ABC   | A Value   |
+--+
|  2   | DEF   | Some Value|
+--+
|  3   | DEF   | Another Value |
+--+
|  4   | JKL   | More Values   |
+--+




 
That's it!  Should be easy but I get an error that says "You can't specify 
target table 'table1' for update in FROM clause"
 
- Thanks


>>> <[EMAIL PROTECTED]> 6/17/05 11:03:40 AM >>>
"Ed Reed" < [EMAIL PROTECTED] > wrote on 06/17/2005 01:35:40 PM:

> Can anyone tell me how I can make this work or suggest a work around?

> Update table1 Set field1=(Select field1 From table1 Where field2="Some 
Value")
> Where field2 ="Another Value";

> Thanks

OK, your example must be broken (or it wouldn't be here) and since that's 
all you posted I have a hard time divining your intent. Would you please 
provide some sample data and explain what it is you want to do _in words_? 
I can't speak for everyone but I know it would sure help me figure out 
what you want to do.

Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Subselect in an Update query

[mfatene]

Hi,
There is one caveat: It is not currently possible to modify a table and select
from the same table in a subquery.

this phrase is from
http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html

solution
*
create table t as Select field1 From table1 Where field2="Some
Value"

Update table1 Set field1=(Select field1 From t Where field2="Some
 Value")
 Where field2  ="Another Value";

**
mysql> select * from upd;
+--+--+
| a| b|
+--+--+
|1 | one  |
|1 | two  |
|2 | one  |
|2 | two  |
|1 | Un   |
+--+--+
5 rows in set (0.09 sec)

mysql> create table t as select * from upd where b='Un';
mysql> update upd set a=(select a from t where b='Un') where b='one';
mysql> select * from upd;
+--+--+
| a| b|
+--+--+
|1 | one  |
|1 | two  |
|1 | one  |<  changed
|2 | two  |
|1 | Un   |
+--+--+
5 rows in set (0.00 sec)



Mathias



Selon Ed Reed <[EMAIL PROTECTED]>:

> Can anyone tell me how I can make this work or suggest a work around?
>
> Update table1 Set field1=(Select field1 From table1 Where field2="Some
> Value")
> Where field2  ="Another Value";
>
> Thanks
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subselect in an Update query

[SGreen]

"Ed Reed" <[EMAIL PROTECTED]> wrote on 06/17/2005 01:35:40 PM:

> Can anyone tell me how I can make this work or suggest a work around?

> Update table1 Set field1=(Select field1 From table1 Where field2="Some 
Value")
> Where field2  ="Another Value";

> Thanks

OK, your example must be broken (or it wouldn't be here) and since that's 
all you posted I have a hard time divining your intent. Would you please 
provide some sample data and explain what it is you want to do _in words_? 
I can't speak for everyone but I know it would sure help me figure out 
what you want to do.

Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Subselect in an Update query

[Ed Reed]

Can anyone tell me how I can make this work or suggest a work around?
 
Update table1 Set field1=(Select field1 From table1 Where field2="Some Value") 
Where field2  ="Another Value";
 
Thanks

RE: UPDATE Query

[Partha Dutta]

If you are trying to set the first 6 characters of your column to '11'
then you can't use SUBSTRING on the LHS, but only from the RHS:

UPDATE CSV_Upload_Data SET PRACT_ASCII =
CONCAT(SUBSTRING(PRACT_ASCII, 1, 15), '11',
SUBSTRING(PRACT_ASCII, 22))
WHERE Insertion_ID = 190716;

--
Partha Dutta, Senior Consultant
MySQL Inc, NY, USA, www.mysql.com
 
Are you MySQL certified?  www.mysql.com/certification
 

> -Original Message-
> From: shaun thornburgh [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 12, 2005 9:47 AM
> To: mysql@lists.mysql.com
> Subject: UPDATE Query
> 
> Hi,
> 
> I am getting an error on the following query and but can't understand why,
> the syntax looks fine to me!
> 
> mysql> UPDATE CSV_Upload_Data SET SUBSTRING(PRACT_ASCII, 16, 6) = '11'
> WHERE Insertion_ID = 190716;
> 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 'SUBSTRING(PRACT_ASCII, 16, 6) = '11' WHERE Insertion_ID =
> 190716'
> at line 1
> mysql>
> 
> Any advice would be greatly appreciated.
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

UPDATE Query

[shaun thornburgh]

Hi,
I am getting an error on the following query and but can't understand why, 
the syntax looks fine to me!

mysql> UPDATE CSV_Upload_Data SET SUBSTRING(PRACT_ASCII, 16, 6) = '11' 
WHERE Insertion_ID = 190716;
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 'SUBSTRING(PRACT_ASCII, 16, 6) = '11' WHERE Insertion_ID = 190716' 
at line 1
mysql>

Any advice would be greatly appreciated.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

General substring update query required

[zzapper]

Hi,
I want to replace every occurence of a string with another.
The following query works fine (appears to anyway)

update tbl_websitepages set page_content=replace 
(page_content,'develop','bucket');

But is there any way to get any Regexp into the substitution?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Help with an UPDATE query please

[Jay Blanchard]

[snip]
I am using version 3.23.54, unfortunately I am unable to supply test
data 
but basically I need to update the Task_ID column for the Bookings table
so 
that it is the same as the Task_ID for Tasks and the Task_Name is
'Booking' 
and the Bookings Project_ID is the same as the Tasks Project_ID.

This is what I need to do:

UPDATE Bookings B
SET B.Task_ID = T.Task_ID
WHERE T.Task_Name = 'Booking'
AND B.Project_ID = T.Project_ID

However I dont know how to introduce the Tasks T alias into the query!
[/snip]

Fortunately we have been having a discussion about multi-table updates
for the past couple of days. Unfortunately your version of MySQL does
not support multi-table updates (http://www.mysql.com/update - "Starting
with MySQL 4.0.4, you can also perform UPDATE operations that cover
multiple tables:")




"It causes you to go to the end so that you can read from the
beginning."
"Why?"
"Top posting is bad"

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Help with an UPDATE query please

[shaun thornburgh]

Sorry Jay,
I am using version 3.23.54, unfortunately I am unable to supply test data 
but basically I need to update the Task_ID column for the Bookings table so 
that it is the same as the Task_ID for Tasks and the Task_Name is 'Booking' 
and the Bookings Project_ID is the same as the Tasks Project_ID.

This is what I need to do:
UPDATE Bookings B
SET B.Task_ID = T.Task_ID
WHERE T.Task_Name = 'Booking'
AND B.Project_ID = T.Project_ID
However I dont know how to introduce the Tasks T alias into the query!
Thanks for your help,
Shaun
From: "Jay Blanchard" <[EMAIL PROTECTED]>
To: "shaun thornburgh" <[EMAIL PROTECTED]>, 

Subject: RE: Help with an UPDATE query please
Date: Wed, 29 Dec 2004 13:53:07 -0600

[snip]
I am having trouble with an UPDATE query. I have three tables as defined
below. My database holds data for bookings. Each booking relates to a
project. Each project has many tasks, one of which is a booking. I have
now
added the column Task_ID to the bookings table. How can I update all the
bookings so that the Task_ID is the same as the Task_ID in the Tasks
table
for that project and the task is a booking?
[/snip]
Can you show us some test data and an example of what you want to
happen? Also, what version of MySQL are you running?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Help with an UPDATE query please

[Jay Blanchard]

[snip]
I am having trouble with an UPDATE query. I have three tables as defined

below. My database holds data for bookings. Each booking relates to a 
project. Each project has many tasks, one of which is a booking. I have
now 
added the column Task_ID to the bookings table. How can I update all the

bookings so that the Task_ID is the same as the Task_ID in the Tasks
table 
for that project and the task is a booking?
[/snip]

Can you show us some test data and an example of what you want to
happen? Also, what version of MySQL are you running?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help with an UPDATE query please

[shaun thornburgh]

Hi,
I am having trouble with an UPDATE query. I have three tables as defined 
below. My database holds data for bookings. Each booking relates to a 
project. Each project has many tasks, one of which is a booking. I have now 
added the column Task_ID to the bookings table. How can I update all the 
bookings so that the Task_ID is the same as the Task_ID in the Tasks table 
for that project and the task is a booking?

Thanks for your help
mysql> DESCRIBE Bookings;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | 
Extra  |
+-+-+--+-+-++
| Booking_ID  | int(11) |  | PRI | NULL| 
auto_increment |
| Booking_Type| varchar(15) |  | | Unavailability  | 
   |
| User_ID | int(11) |  | | 0   | 
   |
| Project_ID  | int(11) | YES  | | NULL| 
   |
| Rep_ID  | int(11) | YES  | | NULL| 
   |
| PCT_ID  | int(11) | YES  | | NULL| 
   |
| Practice_ID | int(11) | YES  | | NULL| 
   |
| Booking_Creator_ID  | int(11) | YES  | | NULL| 
   |
| Booking_Creation_Date   | datetime| YES  | | NULL| 
   |
| Booking_Start_Date  | datetime|  | | -00-00 00:00:00 | 
   |
| Booking_End_Date| datetime|  | | -00-00 00:00:00 | 
   |
| Booking_Completion_Date | date| YES  | | NULL| 
   |
| Booking_Mileage | int(5)  | YES  | | NULL| 
   |
| Booking_Status  | varchar(15) |  | | Other   | 
   |
| Unavailability_ID   | int(2)  | YES  | | NULL| 
   |
| Task_ID | int(11) | YES  | | NULL| 
   |
| Work_Type_ID| int(2)  | YES  | | NULL| 
   |
| Additional_Notes| text| YES  | | NULL| 
   |
| Form_Recieved   | char(3) |  | | | 
   |
| Section_C   | char(3) |  | | | 
   |
| Date_Difference | varchar(20) |  | | n/a | 
   |
+-+-+--+-+-++
21 rows in set (0.01 sec)

mysql> DESCRIBE Projects;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra  |
+--+--+--+-+-++
| Project_ID   | int(11)  |  | PRI | NULL| auto_increment |
| Project_Name | varchar(100) |  | | ||
| Client_ID| int(11)  |  | | 0   ||
| Rep_Viewable | char(3)  |  | | Yes ||
| User_ID  | int(11)  | YES  | | NULL||
+--+--+--+-+-++
5 rows in set (0.00 sec)
mysql> DESCRIBE Tasks;
++-+--+-+-++
| Field  | Type| Null | Key | Default | Extra  |
++-+--+-+-++
| Task_ID| int(11) |  | PRI | NULL| auto_increment |
| Task_Name  | varchar(40) |  | | ||
| Project_ID | int(11) |  | | 0   ||
++-+--+-+-++
3 rows in set (0.00 sec)
mysql>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update Query with special conditions.

[GH]

I am curious about doing something simular to this... does anyone have an idea


On Wed, 24 Nov 2004 00:43:32 -0500, list 123.  wrote:
> Using mySQL 4.0, I would like to know how I can code a query that will change 
> the value of Participants.Active from Y to N is for three or more CONSECUTIVE 
> sessions they have Attendance.Present = 'No'?
> 
> The Attendance Table has Attendance.Session which coresponds to 
> Sessions.SessionID and Attendance.Participant coresponds to 
> Participants.Part_ID;
> 
> To assist, I have shown you the data of the Sessions and the descriptions of 
> Attendance, Participants, Attendance
> 
> Thanks
> G
> 
> mysql> describe Participants;
> +---+---+--+-+-++
> | Field | Type  | Null | Key | Default | Extra  |
> +---+---+--+-+-++
> | Part_ID   | smallint(10) unsigned |  | PRI | NULL| auto_increment |
> | LastName  | varchar(30)   |  | PRI | ||
> | FirstName | varchar(30)   |  | PRI | ||
> | DOB   | date  | YES  | | NULL||
> | Sex   | enum('M','F') |  | | M   ||
> | Phone1| varchar(12)   |  | MUL | ||
> | Phone2| varchar(12)   | YES  | | NULL||
> | Notes | text  |  | | ||
> | Facesheet | enum('Have','Need')   |  | | Need||
> | Active| set('Y','N')  |  | | Y   ||
> +---+---+--+-+-++
> 10 rows in set (0.00 sec)
> 
> mysql> describe Attendance;
> +-+--+--+-+-++
> | Field   | Type | Null | Key | Default | Extra  |
> +-+--+--+-+-++
> | AttID   | int(4)   |  | PRI | NULL| auto_increment |
> | Session | int(2)   |  | MUL | 0   ||
> | Participant | int(2)   |  | | 0   ||
> | Present | enum('Yes','No') |  | | Yes ||
> +-+--+--+-+-++
> 4 rows in set (0.00 sec)
> 
> mysql> describe Sessions;
> +-+-+--+-+++
> | Field   | Type| Null | Key | Default| Extra  |
> +-+-+--+-+++
> | SessionID   | int(2) unsigned |  | PRI | NULL   | auto_increment |
> | SessionDate | date|  | PRI | -00-00 ||
> +-+-+--+-+++
> 2 rows in set (0.03 sec)
> 
> mysql> select * from Sessions;
> +---+-+
> | SessionID | SessionDate |
> +---+-+
> | 1 | 2004-10-30  |
> | 2 | 2004-11-06  |
> | 3 | 2004-11-13  |
> | 4 | 2004-11-20  |
> | 5 | 2004-12-04  |
> | 6 | 2004-12-11  |
> | 7 | 2005-01-08  |
> | 8 | -00-00  |
> | 9 | 2005-01-29  |
> |10 | 2005-02-05  |
> |11 | 2005-02-12  |
> |12 | 2005-02-26  |
> |13 | 2005-03-05  |
> |14 | 2005-03-12  |
> |15 | 2005-03-19  |
> |16 | 2005-04-02  |
> |17 | 2005-04-09  |
> |18 | 2005-04-16  |
> |19 | 2005-04-23  |
> |20 | 2005-05-07  |
> |21 | 2005-05-14  |
> |22 | 2005-05-21  |
> +---+-+
> 22 rows in set (0.05 sec)
> 
> +-+
> | Tables_in_AHRC  |
> +-+
> | Attendance  |
> | Participants|
> | ProgressNotes   |
> | Sessions|
> | Staff   |
> | StaffAttendance |
> +-+
> 6 rows in set (0.00 sec)
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Update Query with special conditions.

[list 123.]

Using mySQL 4.0, I would like to know how I can code a query that will change 
the value of Participants.Active from Y to N is for three or more CONSECUTIVE 
sessions they have Attendance.Present = 'No'?

The Attendance Table has Attendance.Session which coresponds to 
Sessions.SessionID and Attendance.Participant coresponds to 
Participants.Part_ID; 

To assist, I have shown you the data of the Sessions and the descriptions of 
Attendance, Participants, Attendance

Thanks
G

mysql> describe Participants;
+---+---+--+-+-++
| Field | Type  | Null | Key | Default | Extra  |
+---+---+--+-+-++
| Part_ID   | smallint(10) unsigned |  | PRI | NULL| auto_increment |
| LastName  | varchar(30)   |  | PRI | ||
| FirstName | varchar(30)   |  | PRI | ||
| DOB   | date  | YES  | | NULL||
| Sex   | enum('M','F') |  | | M   ||
| Phone1| varchar(12)   |  | MUL | ||
| Phone2| varchar(12)   | YES  | | NULL||
| Notes | text  |  | | ||
| Facesheet | enum('Have','Need')   |  | | Need||
| Active| set('Y','N')  |  | | Y   ||
+---+---+--+-+-++
10 rows in set (0.00 sec)

mysql> describe Attendance;
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++
| AttID   | int(4)   |  | PRI | NULL| auto_increment |
| Session | int(2)   |  | MUL | 0   ||
| Participant | int(2)   |  | | 0   ||
| Present | enum('Yes','No') |  | | Yes ||
+-+--+--+-+-++
4 rows in set (0.00 sec)

mysql> describe Sessions;
+-+-+--+-+++
| Field   | Type| Null | Key | Default| Extra  |
+-+-+--+-+++
| SessionID   | int(2) unsigned |  | PRI | NULL   | auto_increment |
| SessionDate | date|  | PRI | -00-00 ||
+-+-+--+-+++
2 rows in set (0.03 sec)

mysql> select * from Sessions;
+---+-+
| SessionID | SessionDate |
+---+-+
| 1 | 2004-10-30  |
| 2 | 2004-11-06  |
| 3 | 2004-11-13  |
| 4 | 2004-11-20  |
| 5 | 2004-12-04  |
| 6 | 2004-12-11  |
| 7 | 2005-01-08  |
| 8 | -00-00  |
| 9 | 2005-01-29  |
|10 | 2005-02-05  |
|11 | 2005-02-12  |
|12 | 2005-02-26  |
|13 | 2005-03-05  |
|14 | 2005-03-12  |
|15 | 2005-03-19  |
|16 | 2005-04-02  |
|17 | 2005-04-09  |
|18 | 2005-04-16  |
|19 | 2005-04-23  |
|20 | 2005-05-07  |
|21 | 2005-05-14  |
|22 | 2005-05-21  |
+---+-+
22 rows in set (0.05 sec)

+-+
| Tables_in_AHRC  |
+-+
| Attendance  |
| Participants|
| ProgressNotes   |
| Sessions|
| Staff   |
| StaffAttendance |
+-+
6 rows in set (0.00 sec) 








 
   

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: long update query does not replicate correctly (cont.)

[Sasha Pachev]

Przemyslaw Popielarski wrote:
Sasha Pachev <[EMAIL PROTECTED]> wrote:
Check if you have any replication restricting rules on the slave.
There might be a bug that incorrectly flags a query to be excluded.
If that is the case, then try to re-write the rules to see if you can
get around the bug.

Yes. I have recently add the following line to the slave's my.cnf file:
replicate-wild-do-table=abe_exp.%
There are no more replication rules in that file. Both tables included in
the multitable update were in this database (abe_exp).
So this is a bug.
I suggest then that you file a bug report at http://bugs.mysql.com
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: long update query does not replicate correctly (cont.)

[Przemyslaw Popielarski]

Sasha Pachev <[EMAIL PROTECTED]> wrote:
> Check if you have any replication restricting rules on the slave.
> There might be a bug that incorrectly flags a query to be excluded.
> If that is the case, then try to re-write the rules to see if you can
> get around the bug.

Yes. I have recently add the following line to the slave's my.cnf file:
replicate-wild-do-table=abe_exp.%
There are no more replication rules in that file. Both tables included in
the multitable update were in this database (abe_exp).

So this is a bug.

-- 
./ premax
./ [EMAIL PROTECTED]
./ koniec i bomba, a kto czytal ten traba. w.g.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: long update query does not replicate correctly (cont.)

[Sasha Pachev]

Przemyslaw Popielarski wrote:
I'm executing on master a long and complicated query such:
UPDATE (lots of tables, lots of LEFT JOIN)  SET ... WHERE .

Okey, I simplified the query to:
-
UPDATE 
tKsidata,
tKsiabeksiazki  
SET 
tKsidata.STAN_REALNY=tKsiabeksiazki.STAN
WHERE
tKsidata.ksi='0815120877' and
tKsidata.KSI=tKsiabeksiazki.KSI 
-

This query shows up in both master's and slave's log, but it DOES NOT 
execute on slave. Table tKsidata on slave remains unmodified. 

Executing the query manually works perfectly on slave.
Is seems that multi-table update badly replicates in MySQL 4.1.
Przemyslaw:
Check if you have any replication restricting rules on the slave. There might be 
a bug that incorrectly flags a query to be excluded. If that is the case, then 
try to re-write the rules to see if you can get around the bug.

Also, try building 4.1.7 from source - there was another user that was reporting 
a really strange crash that went away after he complied the binary himself. So 
there is some strong evidence that the standard MySQL 4.1.7 binary was not built 
properly, in which case, odd behaviour is to be expected.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: long update query does not replicate correctly (cont.)

[Przemyslaw Popielarski]

> I'm executing on master a long and complicated query such:
> UPDATE (lots of tables, lots of LEFT JOIN)  SET ... WHERE .

Okey, I simplified the query to:
-
UPDATE 
tKsidata,
tKsiabeksiazki  
SET 
tKsidata.STAN_REALNY=tKsiabeksiazki.STAN
WHERE
tKsidata.ksi='0815120877' and
tKsidata.KSI=tKsiabeksiazki.KSI 
-

This query shows up in both master's and slave's log, but it DOES NOT 
execute on slave. Table tKsidata on slave remains unmodified. 

Executing the query manually works perfectly on slave.

Is seems that multi-table update badly replicates in MySQL 4.1.



./ premax
./ [EMAIL PROTECTED]
./ koniec i bomba, a kto czytal ten traba. w.g.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

long update query does not replicate correctly

[Przemyslaw Popielarski]

I'm executing on master a long and complicated query such:
UPDATE (lots of tables, lots of LEFT JOIN)  SET ... WHERE .

The query updates i.e. 3816 rows.

The query DOES replicate do master's and slave's binary log. 
But it DOES NOT execute on slave. 3816 rows are not updated on slave 
and slave behaves like there was nothing more to execute.

I do `mysqlbinlog slave-log` on slave and see this query in log. 
Next, I copy & paste to MySQL client and execute this query manualy 
on slave. And...  3816 rows affected.

I'm trying to isolate problem. MySQL 4.1.7-standard-binary, Linux 
x86. In MySQL 4.0 everything this was for sure okey!


The query:

UPDATE tKsidata, tKsiabeksiazki 
LEFT JOIN tKsiabeautorzy as a1 ON (tKsiabeksiazki.KSI=a1.KSI AND 
a1.LP='1' AND a1.GR='0') 
LEFT JOIN tKsiabeautorzy as a2 ON (tKsiabeksiazki.KSI=a2.KSI AND 
a2.LP='2' AND a2.GR='0') 
LEFT JOIN tKsiabeautorzy as a3 ON (tKsiabeksiazki.KSI=a3.KSI AND 
a3.LP='3' AND a3.GR='0') 
LEFT JOIN tKsigdanskksiazki ON 
tKsiabeksiazki.KSI=tKsigdanskksiazki.KSI 
LEFT JOIN tKsigliwiceksiazki ON 
tKsiabeksiazki.KSI=tKsigliwiceksiazki.KSI 
LEFT JOIN tKsikrakowksiazki ON 
tKsiabeksiazki.KSI=tKsikrakowksiazki.KSI 
LEFT JOIN tKsiwysksiazki ON tKsiabeksiazki.KSI=tKsiwysksiazki.KSI 
LEFT JOIN tKsikolejowaksiazki ON 
tKsiabeksiazki.KSI=tKsikolejowaksiazki.KSI 
LEFT JOIN tBooksextra ON tKsiabeksiazki.KSI=tBooksextra.KSI 
LEFT JOIN tUnits ON tKsiabeksiazki.DZIAL=tUnits.DZIAL 
LEFT JOIN tSeries ON tKsiabeksiazki.SERIA=tSeries.SERIA 
LEFT JOIN tCovers ON tKsiabeksiazki.KSI=tCovers.KSI, tGrupy 
SET tKsidata.KSI=LCASE(tKsiabeksiazki.KSI), 
tKsidata.PKW=tKsiabeksiazki.PKW, 
tKsidata.TYTUL=tKsiabeksiazki.TYTUL, 
tKsidata.WYD=LCASE(tKsiabeksiazki.WYD), 
tKsidata.ROK=tKsiabeksiazki.ROK, 
tKsidata.NRW=tKsiabeksiazki.NRW, 
tKsidata.STRON=tKsiabeksiazki.STRON, 
tKsidata.ISBN=tKsiabeksiazki.ISBN, 
tKsidata.D_POCZ=tKsiabeksiazki.D_POCZ, 
tKsidata.VAT_REALNY=CASE tKsiabeksiazki.SVAT 
WHEN 1 THEN 0 WHEN 2 THEN 0 WHEN 3 THEN 2 WHEN 4 THEN 7 WHEN 5 THEN 
22 WHEN 6 THEN 12 WHEN 7 THEN 17 WHEN 8 THEN 3 ELSE NULL END, 
tKsidata.CENA_REALNA=IF(tKsiabeksiazki.C_ZAOK0,TRUNCATE(((tKsiabeksiaz
ki.C_DET-
tKsiabeksiazki.C_DET*tKsiabeksiazki.RABAT/100)/tKsiabeksiazki.C_ZAOK0)
+0.5,0)*tKsiabeksiazki.C_ZAOK0,tKsiabeksiazki.C_DET-
tKsiabeksiazki.C_DET*tKsiabeksiazki.RABAT/100), 
tKsidata.C_DET=IF(tKsiabeksiazki.C_ZAOK0,TRUNCATE(tKsiabeksiazki.C_DET
/tKsiabeksiazki.C_ZAOK0+0.5,0)*tKsiabeksiazki.C_ZAOK0, 
tKsiabeksiazki.C_DET), 
tKsidata.C_WAL0=tKsiabeksiazki.C_WAL0, 
tKsidata.RABAT=tKsiabeksiazki.RABAT, 
tKsidata.STAN_REALNY= IF(tKsiabeksiazki.STAN-tKsiabeksiazki.S_BLK 
+IFNULL(tKsigdanskksiazki.STAN,0) -IFNULL(tKsigdanskksiazki.S_BLK,0) 
+IFNULL(tKsigliwiceksiazki.STAN,0) -
IFNULL(tKsigliwiceksiazki.S_BLK,0) +IFNULL(tKsikrakowksiazki.STAN,0) -
IFNULL(tKsikrakowksiazki.S_BLK,0) +IFNULL(tKsiwysksiazki.STAN,0) -
IFNULL(tKsiwysksiazki.S_BLK,0) +IFNULL(tKsikolejowaksiazki.STAN,0) -
IFNULL(tKsikolejowaksiazki.S_BLK,0) < 0, 0, tKsiabeksiazki.STAN-
tKsiabeksiazki.S_BLK +IFNULL(tKsigdanskksiazki.STAN,0) -
IFNULL(tKsigdanskksiazki.S_BLK,0) +IFNULL(tKsigliwiceksiazki.STAN,0) -
IFNULL(tKsigliwiceksiazki.S_BLK,0) +IFNULL(tKsikrakowksiazki.STAN,0) -
IFNULL(tKsikrakowksiazki.S_BLK,0) +IFNULL(tKsiwysksiazki.STAN,0) -
IFNULL(tKsiwysksiazki.S_BLK,0) +IFNULL(tKsikolejowaksiazki.STAN,0) -
IFNULL(tKsikolejowaksiazki.S_BLK,0) ), 
tKsidata.STAN_WAW=IF(tKsiabeksiazki.STAN-tKsiabeksiazki.S_BLK <= 0, 
0, tKsiabeksiazki.STAN-tKsiabeksiazki.S_BLK), 
tKsidata.NAZWISKO_1=a1.NAZW, 
tKsidata.IMIE1_1=IF(CHAR_LENGTH(a1.IM1)=1,CONCAT(a1.IM1,'.'),a1.IM1), 

tKsidata.IMIE2_1=IF(CHAR_LENGTH(a1.IM2)=1,CONCAT(a1.IM2,'.'),a1.IM2), 

tKsidata.NAZWISKO_2=a2.NAZW, 
tKsidata.IMIE1_2=IF(CHAR_LENGTH(a2.IM1)=1,CONCAT(a2.IM1,'.'),a2.IM1), 

tKsidata.IMIE2_2=IF(CHAR_LENGTH(a2.IM2)=1,CONCAT(a2.IM2,'.'),a2.IM2), 

tKsidata.NAZWISKO_3=a3.NAZW, 
tKsidata.IMIE1_3=IF(CHAR_LENGTH(a3.IM1)=1,CONCAT(a3.IM1,'.'),a3.IM1), 

tKsidata.IMIE2_3=IF(CHAR_LENGTH(a3.IM2)=1,CONCAT(a3.IM2,'.'),a3.IM2), 

tKsidata.BIT=tKsiabeksiazki.BIT, tKsidata.GRKOD=tGrupy.GRKOD, 
tKsidata.DZIALID=tUnits.DZIALID, tKsidata.SERIAID=tSeries.SERIAID, 
tKsidata.KSIDESCPL=CONCAT_WS('',IF(tBooksextra.BOOKCOMMENT1PL,t
Booksextra.BOOKCOMMENT1PL,tBooksextra.BOOKCOMMENT1EN),IF(tBooksextra.B
OOKCOMMENT2PL,tBooksextra.BOOKCOMMENT2PL,tBooksextra.BOOKCOMMENT2EN),I
F(tBooksextra.BOOKCOMMENT3PL,tBooksextra.BOOKCOMMENT3PL,tBooksextra.BO
OKCOMMENT3EN)), 
tKsidata.KSIDESCEN=CONCAT_WS('',tBooksextra.BOOKCOMMENT1EN,tBoo
ksextra.BOOKCOMMENT2EN,tBooksextra.BOOKCOMMENT3EN), 
tKsidata.TOC=tBooksextra.TOC, 
tKsidata.MIESIAC=tBooksextra.MIESIAC, 
tKsidata.BINDING=tBooksextra.BINDING, 
tKsidata.STATUS=tBooksextra.STATUS, 
tKsidata.COVER=IF(tCovers.KSI,'1','0'), 
tKsidata.SHOWPRICE=IFNULL(tBooksextra.SHOWPRICE,1), 
tKsidata.VISIBLE=IFNULL(tBooksextra.VISIBLE,1) 
WHERE tKsidata.KSI=tKsiabeksiazki.KSI 
AND tKsiabeksiazki.ID1=tGrupy.ID1 AND tKsiabeksiazki.ID2=tGrupy.ID2;


./ premax
./ [EMAIL PROTECTED]
./

RE: Update query help

[Jeff McKeon]

Yeah I thought of that but was hoping not to have to use a temp table.

Thanks!

Jeff

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 05, 2004 9:25 AM
To: Jeff McKeon
Cc: [EMAIL PROTECTED]
Subject: Re: Update query help


Break it down into two steps. Compute your new values by customerid,
then 
update your customer table with your computed data.

CREATE TEMPORARY TABLE tmpFirstTran
SELECT CustID, min(Datestamp) as mindate
from Transactions
group by CustID;

update Customer c INNER JOIN tmpFirstTran ft ON ft.CustID = c.ID SET
c.First_Transaction = ft.mindate;

DROP TEMPORARY TABLE tmpFirstTran;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Jeff McKeon" <[EMAIL PROTECTED]> wrote on 11/05/2004 09:04:06 AM:

> I have two tables.  One has a list of customers. The other has a 
> record of customer transactions including unix datestamps of each 
> transaction.
> 
> I've added a field to the customer table called "First_Transaction"
> 
> I want to update this field with the datestamp of the first 
> transaction for each customer from the "Transaction" table.
> 
> I tried this...
> 
> UPDATE Customer,Transactions set Customer.First_Transaction =
> MIN(Transactions.Datestamp)
> Where Customer.ID = Transactions.CustID
> 
> But this doesn't work because of MIN() grouping.  I'm stumped, anyone 
> know how to accomplish this?
> 
> Thanks,
> 
> Jeff
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update query help

[SGreen]

Break it down into two steps. Compute your new values by customerid, then 
update your customer table with your computed data.

CREATE TEMPORARY TABLE tmpFirstTran
SELECT CustID, min(Datestamp) as mindate
from Transactions
group by CustID;

update Customer c INNER JOIN tmpFirstTran ft ON ft.CustID = c.ID
SET c.First_Transaction = ft.mindate;

DROP TEMPORARY TABLE tmpFirstTran;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Jeff McKeon" <[EMAIL PROTECTED]> wrote on 11/05/2004 09:04:06 AM:

> I have two tables.  One has a list of customers. The other has a record
> of customer transactions including unix datestamps of each transaction.
> 
> I've added a field to the customer table called "First_Transaction"
> 
> I want to update this field with the datestamp of the first transaction
> for each customer from the "Transaction" table.
> 
> I tried this...
> 
> UPDATE Customer,Transactions set Customer.First_Transaction =
> MIN(Transactions.Datestamp)
> Where Customer.ID = Transactions.CustID
> 
> But this doesn't work because of MIN() grouping.  I'm stumped, anyone
> know how to accomplish this?
> 
> Thanks,
> 
> Jeff
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Update query help

[Jeff McKeon]

I have two tables.  One has a list of customers. The other has a record
of customer transactions including unix datestamps of each transaction.

I've added a field to the customer table called "First_Transaction"

I want to update this field with the datestamp of the first transaction
for each customer from the "Transaction" table.

I tried this...

UPDATE Customer,Transactions set Customer.First_Transaction =
MIN(Transactions.Datestamp)
Where Customer.ID = Transactions.CustID

But this doesn't work because of MIN() grouping.  I'm stumped, anyone
know how to accomplish this?

Thanks,

Jeff

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Complex update query

[Mauricio Pellegrini]

Hi,

I don't know if this is possible ( my experience with SQL is very short)
but I need to update table A from table B but saving the "Old values"
from certain columns into table C.


In other words I have 3 tables A,B,C.


Table A is going to be updated with values from table B, but
I have to keep track of these changes made to table A into table C.


By tracking I mean that any row changed on A must be saved into table C
( actually not the whole row but certain columns)

for example,

Update tableA 
inner join tableB on ( id_tableA = id_tableB )
set tableA.col1 = tableB.col1, 
etc.., 
etc

But when tableA.col1 is changed I would like to have the original
value from id_tableA and col1 INSERTED into tableC as a new row.


Is it posible to do that at the same time?

I mean without creating a temporary table first then updating tableA
and atfer that using the temporary table to insert the new records into
tableC ?


Thank you
And forgive me if I couldn't make myself clear

Mauricio






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How can I turn this into an update query?

[Philippe Poelvoorde]

Ed Lazor wrote:
Is there a way for me to change this select query into an update query?
select t1.ID, t1.Title, t2.Title from products t1, systems t2 where t1.Title
like CONCAT(t2.Title, "%")
A lot of products have the system title as the first part of the product
title.  I'm trying to update the product SystemID field and set it to the
corresponding system ID.  I'm sure there's a way to do this, but I'm not
sure how.  Any help is greatly appreciated.
Ed

try this :
update products, systems set products.SystemId=systems.ID where 
products.title like concat(systems.Title,'%');
without any warranty...

--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How can I turn this into an update query?

[Ed Lazor]

Is there a way for me to change this select query into an update query?

select t1.ID, t1.Title, t2.Title from products t1, systems t2 where t1.Title
like CONCAT(t2.Title, "%")

A lot of products have the system title as the first part of the product
title.  I'm trying to update the product SystemID field and set it to the
corresponding system ID.  I'm sure there's a way to do this, but I'm not
sure how.  Any help is greatly appreciated.

Ed


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query return value

[Jeff Demel]

The issue is that the code doesn't know (and doesn't care) if the data 
is actually being changed, it's just accepting the posted form data, 
compiling it, and updating the record.  It uses the return value (number 
of rows updated) to make sure there wasn't a problem updating the 
record.  However, because of the way MySQL works, if the data matches 
what's already there then MySQL returns 0, which the code interprets as 
an "Error updating the record".

So, I have to write code to see if the data posted matches the data in 
the row before running the update or add that LastUpdated work-around.

Unless, as has been suggested, there's a way to change the functionality 
of the DataProvider.

-Jeff
Brent Baisley wrote:
I'm not following why you need to force an update? You mentioned a row 
refresh, but I'm not sure in what context. If you are looking to find 
out if a row has changed since you last read it, then you should have a 
timestamp field. The first timestamp field is always updated when data 
changes in a record, so you could use this as sort of a record 
versioning system. Just query the timestamp field to check if the data 
has changed, if it has, then do the full query to retrieve the entire 
record.

Even if you create a field that you change on every update, MySQL only 
changes the data in fields that have changed, not in all the fields in 
your update statement. Paul DuBois she be able to correct if I'm wrong 
on this. I can't think of any reason to force a rewrite the same data to 
disk.

On Sep 20, 2004, at 3:49 PM, Jeff Demel wrote:
That's what I was afraid of.
Now I have to add a bunch of code to check the data before sending 
(pull the record, compare the data, then decide to run the update or 
not). How efficient is that, I wonder?

Is there any way to force it to update the row?
I'm thinking a workaround might be to add a TimesUpdated column as an 
Int, and update that every time (TimesUpdated = TimesUpdated+1).  That 
would force a return value of 1.

-Jeff
Brent Baisley wrote:
No, MySQL will indicate if anything in the row has changed. If you 
are updating with the same data, than nothing changes and MySQL 
doesn't waste the time to lock the table, write the data and update 
the indexes. It's much more efficient this way.
On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:

I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something 
the MySQLDirect .NET provider is doing, and to cover that 
possibility I've sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to 
refresh it, and am getting 0 as a return value. If I send different 
data, then I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,"thisname","thisdescription"
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but 
really think I should be getting a 1.  I would think that if there 
was no ID with a value of 5, then it would return 0.  But if there 
is a row with an ID of 5, then it should refresh the row and return 
1.  Right?

-Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query return value

[Brent Baisley]

I'm not following why you need to force an update? You mentioned a row 
refresh, but I'm not sure in what context. If you are looking to find 
out if a row has changed since you last read it, then you should have a 
timestamp field. The first timestamp field is always updated when data 
changes in a record, so you could use this as sort of a record 
versioning system. Just query the timestamp field to check if the data 
has changed, if it has, then do the full query to retrieve the entire 
record.

Even if you create a field that you change on every update, MySQL only 
changes the data in fields that have changed, not in all the fields in 
your update statement. Paul DuBois she be able to correct if I'm wrong 
on this. I can't think of any reason to force a rewrite the same data 
to disk.

On Sep 20, 2004, at 3:49 PM, Jeff Demel wrote:
That's what I was afraid of.
Now I have to add a bunch of code to check the data before sending 
(pull the record, compare the data, then decide to run the update or 
not). How efficient is that, I wonder?

Is there any way to force it to update the row?
I'm thinking a workaround might be to add a TimesUpdated column as an 
Int, and update that every time (TimesUpdated = TimesUpdated+1).  That 
would force a return value of 1.

-Jeff
Brent Baisley wrote:
No, MySQL will indicate if anything in the row has changed. If you 
are updating with the same data, than nothing changes and MySQL 
doesn't waste the time to lock the table, write the data and update 
the indexes. It's much more efficient this way.
On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:
I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something 
the MySQLDirect .NET provider is doing, and to cover that 
possibility I've sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to 
refresh it, and am getting 0 as a return value. If I send different 
data, then I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,"thisname","thisdescription"
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but 
really think I should be getting a 1.  I would think that if there 
was no ID with a value of 5, then it would return 0.  But if there 
is a row with an ID of 5, then it should refresh the row and return 
1.  Right?

-Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query return value

[Paul DuBois]

At 14:49 -0500 9/20/04, Jeff Demel wrote:
That's what I was afraid of.
Now I have to add a bunch of code to check the data before sending 
(pull the record, compare the data, then decide to run the update or 
not). How efficient is that, I wonder?

Is there any way to force it to update the row?
I'm thinking a workaround might be to add a TimesUpdated column as 
an Int, and update that every time (TimesUpdated = TimesUpdated+1). 
That would force a return value of 1.
First you should check your provider to find out if it offers some
way to set the connect flag that tells MySQL to return the rows-matched
value rather than the rows-changed value.  If you can do this, the return
from an UPDATE will include rows matched by the WHERE clause, whether or
not the values actually changed.

-Jeff
Brent Baisley wrote:
No, MySQL will indicate if anything in the row has changed. If you 
are updating with the same data, than nothing changes and MySQL 
doesn't waste the time to lock the table, write the data and update 
the indexes. It's much more efficient this way.

On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:
I'm seeing some odd behavior when I run an UPDATE query, and need 
to know if this is something that MySQL does.  It could be 
something the MySQLDirect .NET provider is doing, and to cover 
that possibility I've sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the 
same values that are already in the row, what should the return 
value be? I'm occasionally sending the same exact data back to a 
row to refresh it, and am getting 0 as a return value. If I send 
different data, then I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,"thisname","thisdescription"
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but 
really think I should be getting a 1.  I would think that if there 
was no ID with a value of 5, then it would return 0.  But if there 
is a row with an ID of 5, then it should refresh the row and 
return 1.  Right?

-Jeff

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query return value

[Jeff Demel]

That's what I was afraid of.
Now I have to add a bunch of code to check the data before sending (pull 
the record, compare the data, then decide to run the update or not). 
How efficient is that, I wonder?

Is there any way to force it to update the row?
I'm thinking a workaround might be to add a TimesUpdated column as an 
Int, and update that every time (TimesUpdated = TimesUpdated+1).  That 
would force a return value of 1.

-Jeff
Brent Baisley wrote:
No, MySQL will indicate if anything in the row has changed. If you are 
updating with the same data, than nothing changes and MySQL doesn't 
waste the time to lock the table, write the data and update the indexes. 
It's much more efficient this way.

On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:
I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something the 
MySQLDirect .NET provider is doing, and to cover that possibility I've 
sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to refresh 
it, and am getting 0 as a return value. If I send different data, then 
I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,"thisname","thisdescription"
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but really 
think I should be getting a 1.  I would think that if there was no ID 
with a value of 5, then it would return 0.  But if there is a row with 
an ID of 5, then it should refresh the row and return 1.  Right?

-Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query return value

[Brent Baisley]

No, MySQL will indicate if anything in the row has changed. If you are 
updating with the same data, than nothing changes and MySQL doesn't 
waste the time to lock the table, write the data and update the 
indexes. It's much more efficient this way.

On Sep 20, 2004, at 3:22 PM, Jeff Demel wrote:
I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something the 
MySQLDirect .NET provider is doing, and to cover that possibility I've 
sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to refresh 
it, and am getting 0 as a return value. If I send different data, then 
I get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,"thisname","thisdescription"
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but really 
think I should be getting a 1.  I would think that if there was no ID 
with a value of 5, then it would return 0.  But if there is a row with 
an ID of 5, then it should refresh the row and return 1.  Right?

-Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update query return value

[Jeff Demel]

I'm seeing some odd behavior when I run an UPDATE query, and need to 
know if this is something that MySQL does.  It could be something the 
MySQLDirect .NET provider is doing, and to cover that possibility I've 
sent an email to their support team.

So anyway, here's the scenario.
If I run an UPDATE query, and my UPDATE statement contains the same 
values that are already in the row, what should the return value be? 
I'm occasionally sending the same exact data back to a row to refresh 
it, and am getting 0 as a return value. If I send different data, then I 
get a 1 back, which makes sense.

For example:
*Row values in ThisTable
ID(Int), Name(Text), Description(Text)
5,"thisname","thisdescription"
*SQL
UPDATE ThisTable Set
Name = 'thisname',
Description = 'thisdescription'
WHERE ID = 5;
So should this SQL statement return 0 or 1?  I'm getting 0, but really 
think I should be getting a 1.  I would think that if there was no ID 
with a value of 5, then it would return 0.  But if there is a row with 
an ID of 5, then it should refresh the row and return 1.  Right?

-Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Is this a valid Update Query

[Mark C. Stafford]

On Tue, 7 Sep 2004 15:13:25 -0700, Allen Weeks <[EMAIL PROTECTED]> wrote:
> Is this a valid query 
 
>Update Atable set Afield = concat(Afield, "\n", "Some Text") where KeyField
> = 'keydata'

Hi Allen,

I notice that you're using both single- and double-quotes. I'm in the
habit of using only single. Does it work for you sans-DQ's?

http://dev.mysql.com/doc/mysql/en/String_syntax.html

If the server SQL mode has ANSI_QUOTES enabled, string literals can be
quoted only with single quotes. A string quoted with double quotes
will be interpreted as an identifier.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Is this a valid Update Query

[Allen Weeks]

Hi All,

Is this a valid query and will it achieve the result of appending a carriage
return and some text to the current contents of a "text" type field:

Update Atable set Afield = concat(Afield, "\n", "Some Text") where KeyField
= 'keydata'

Thanks in advance.

Allen


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with Slow Update Query

[Michael Stassen]

An index on domain cannot be used to satisfy your WHERE clause, because you 
are comparing the result of a function performed on domain to a value.  As 
soon as you feed your column to a function, you lose the use of an index on 
that column.  So, each and every one of these queries performs a full table 
scan!

Your best bet would be to have the application which inserts this data 
transform the url to a domain before inserting, so you have the data you want.

Given your current setup, full table scans may be unavoidable, but you may 
be able to improve the situation.  I suspect that with clever use of string 
functions  we could 
dramatically reduce the number of such queries.  If I understand you 
correctly, you want everything up to the first '.'.  This should do the trick:

  UPDATE url_cat SET domain = SUBSTRING_INDEX(domain, '.', 1);
That will replace domain with the contents of domain up to, but not 
including, the first '.' all in one pass.  Yes, it's still a full table 
scan, but it's 1 full table scan.

Michael
Martin Rytz wrote:
Hi all
 
I have a problem with slow update queries like these (5 examples):
 
update url_cat set domain = '01net' where left( domain, instr( domain, '.' )
-1 ) = '01net';
update url_cat set domain = '1-meta' where left( domain, instr( domain, '.'
) -1 ) = '1-meta';
update url_cat set domain = '105' where left( domain, instr( domain, '.' )
-1 ) = '105';
update url_cat set domain = '123love' where left( domain, instr( domain, '.'
) -1 ) = '123love'; 
update url_cat set domain = 'google' where left( domain, instr( domain, '.'
) -1 ) = 'google'; 
 
before the update, the field domain contains 'google.com' or 'google.de' or
'google.ch' and after the update it contains only 'google'.
 
i have to make thousends of this updates an it takes a long time. the table
'url_cat' contains about 100'000 entries! an index would help, but mysql
does not use any static index. the index should contain the
where-condition... but this is not possible, because the lenght of the
condition differs?! 
 
does anybody have another idea?
 
thank you in advance.
martin   


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with Slow Update Query

[SGreen]

First create a table with the "fixed" domain names:

CREATE TABLE FixedDomains
SELECT DISTINCT domain, left( domain, instr( domain, '.'> ) -1 ) as 
newdomain
FROM url_cat

Index your new table (for speed):

ALTER FixedDomains ADD INDEX (Domain)

*** NOTE: You really want to review (and modify, if necessary) the data in 
FixedDomains _before_ you do the next step. Make sure that the newdomain 
column has the correct extract for the value in the domain column. ***

Now you can mass-update your data:

UPDATE url_cat u
INNER JOIN FixedDomains fd
ON fd.domain = u.domain
SET u.domain = fd.newdomain

You can then  "DROP TABLE FixedDomains" whenever you are through using it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Martin Rytz" <[EMAIL PROTECTED]> wrote on 08/10/2004 09:51:41 AM:

> Hi all
> 
> I have a problem with slow update queries like these (5 examples):
> 
> update url_cat set domain = '01net' where left( domain, instr( domain, 
'.' )
> -1 ) = '01net';
> update url_cat set domain = '1-meta' where left( domain, instr( domain, 
'.'
> ) -1 ) = '1-meta';
> update url_cat set domain = '105' where left( domain, instr( domain, '.' 
)
> -1 ) = '105';
> update url_cat set domain = '123love' where left( domain, instr( domain, 
'.'
> ) -1 ) = '123love'; 
> update url_cat set domain = 'google' where left( domain, instr( domain, 
'.'
> ) -1 ) = 'google'; 
> 
> before the update, the field domain contains 'google.com' or 'google.de' 
or
> 'google.ch' and after the update it contains only 'google'.
> 
> i have to make thousends of this updates an it takes a long time. the 
table
> 'url_cat' contains about 100'000 entries! an index would help, but mysql
> does not use any static index. the index should contain the
> where-condition... but this is not possible, because the lenght of the
> condition differs?! 
> 
> does anybody have another idea?
> 
> thank you in advance.
> martin 

Problem with Slow Update Query

[Martin Rytz]

Hi all
 
I have a problem with slow update queries like these (5 examples):
 
update url_cat set domain = '01net' where left( domain, instr( domain, '.' )
-1 ) = '01net';
update url_cat set domain = '1-meta' where left( domain, instr( domain, '.'
) -1 ) = '1-meta';
update url_cat set domain = '105' where left( domain, instr( domain, '.' )
-1 ) = '105';
update url_cat set domain = '123love' where left( domain, instr( domain, '.'
) -1 ) = '123love'; 
update url_cat set domain = 'google' where left( domain, instr( domain, '.'
) -1 ) = 'google'; 
 
before the update, the field domain contains 'google.com' or 'google.de' or
'google.ch' and after the update it contains only 'google'.
 
i have to make thousends of this updates an it takes a long time. the table
'url_cat' contains about 100'000 entries! an index would help, but mysql
does not use any static index. the index should contain the
where-condition... but this is not possible, because the lenght of the
condition differs?! 
 
does anybody have another idea?
 
thank you in advance.
martin   

RE: update query question

[Chris W. Parker]

[EMAIL PROTECTED] 
on Wednesday, July 07, 2004 11:08 AM said:

> Have you tried this other way of making an inner join?

no i did not because i did know you could do a JOIN on an UPDATE. thanks
for your suggestions i will try them out.


chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query question

[SGreen]

Have you tried this other way of making an inner join?

UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id =
p.id
SET pc.prod_sequential_id = p.id

But that does not seem right our you could say:

UPDATE products_categories AS pc
SET pc.prod_sequential_id = pc.prod_id

and have the same statement. I think this is what you meant to say:

UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id =
p.id
SET pc.prod_sequential_id = p.sequential_id

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


|-+>
| |   "Chris W. Parker"|
| |   <[EMAIL PROTECTED]|
| |   .com>|
| ||
| |   07/06/2004 01:14 |
| |   PM   |
| ||
|-+>
  
>|
  |
|
  |   To:   <[EMAIL PROTECTED]>
|
  |   cc:  
|
  |   Fax to:  
        |
  |   Subject:  update query question  
|
  
>|




hello,

i've had to change some of the tables in my db to accomodate some
greater flexibility in the application that uses it and because of this
i need to go through and update all the records. i've done one table by
hand and it had about 100 records and took about 20 minutes. but this
next table has about 550 records and i really don't feel like doing this
all by hand. i'm using MySQL Control Center to do this editing so i'd
like to know if there's a single SQL statement i could use to update all
the rows.

here is a simple representation:

products: (pay no attention to the poor choice in column names. this is
a retrofitting and will be fixed in later versions.)
+--+---+
| id   | sequential_id |
+--+---+
| PRDX-41  | 1 |
| ABCX-01  | 2 |
| FF00-11  | 3 |
\/\/\/\/\/\/\/\/
| ETC0-99  |   500 |
+--+---+

the 'prod_sequential_id' column was added later to the
products_categories table.

products_categories:
+-+-+++
| id  | prod_id | prod_sequential_id | cat_id |
+-+-+++
|   1 | PRDX-41 |  0 | 41 |
|   2 | PRDX-41 |  0 | 15 |
|   3 | ABCX-01 |  0 | 13 |
|   4 | FF00-11 |  0 | 89 |
\/\/\/\/\/\/\/\/
| 610 | ETC0-99 |  0 | 41 |
+-+-+++

so... as you can see, prod_sequential_id has all 0's in its column. it
should contain the value of products.sequential_id WHERE
products_categories.prod_id = products.id.

the problem is that i'm not sure how to do this all in one statement (or
if it's even possible):

(i know the following does not work, but it's basically the logic i
think i need.)

UPDATE products_categories AS pc, products AS p
SET pc.prod_sequential_id = p.id
WHERE pc.prod_id = p.id;


thanks for your help.
chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update query question

[Chris W. Parker]

hello,

i've had to change some of the tables in my db to accomodate some
greater flexibility in the application that uses it and because of this
i need to go through and update all the records. i've done one table by
hand and it had about 100 records and took about 20 minutes. but this
next table has about 550 records and i really don't feel like doing this
all by hand. i'm using MySQL Control Center to do this editing so i'd
like to know if there's a single SQL statement i could use to update all
the rows.

here is a simple representation:

products: (pay no attention to the poor choice in column names. this is
a retrofitting and will be fixed in later versions.)
+--+---+
| id   | sequential_id |
+--+---+
| PRDX-41  | 1 |
| ABCX-01  | 2 |
| FF00-11  | 3 |
\/\/\/\/\/\/\/\/
| ETC0-99  |   500 |
+--+---+

the 'prod_sequential_id' column was added later to the
products_categories table.

products_categories:
+-+-+++
| id  | prod_id | prod_sequential_id | cat_id |
+-+-+++
|   1 | PRDX-41 |  0 | 41 |
|   2 | PRDX-41 |  0 | 15 |
|   3 | ABCX-01 |  0 | 13 |
|   4 | FF00-11 |  0 | 89 |
\/\/\/\/\/\/\/\/
| 610 | ETC0-99 |  0 | 41 |
+-+-+++

so... as you can see, prod_sequential_id has all 0's in its column. it
should contain the value of products.sequential_id WHERE
products_categories.prod_id = products.id.

the problem is that i'm not sure how to do this all in one statement (or
if it's even possible):

(i know the following does not work, but it's basically the logic i
think i need.)

UPDATE products_categories AS pc, products AS p
SET pc.prod_sequential_id = p.id
WHERE pc.prod_id = p.id;


thanks for your help.
chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: GRANT update query: Updating host access entry for users but retaining existing passwords

[Shin]

Hi,

Thanks for the followup/reply.

On Tue, Sep 30, 2003 at 01:56:11PM +0100, Andy Eastham wrote:

> I've never tried this, so it's pure speculation, but I believe all of the
> grant information is contained in a regular table called user.

I had a look into this and it seems that some of the information is
also held in the Db table as well - hopefully someone can correct me
if I'm wrong.

> You should be able to copy this information into a temporary table using
> select into, then perform regular updates to change the host information to
> match your new subnets.  Then simply copy it back to the user table, which
> will add these records to the existing records, crucially with the same
> passwords.  You might have to repeat this a few times to get all the subnets
> in.

I was kinda hoping I could avoid doing such hoops - but thanks for
the suggestion. I will have a play on my test database.

> I'd certainly perform lots of testing away from the live system before I
> tried this for real.

:-))

Rgds
Shin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: GRANT update query: Updating host access entry for users but retaining existing passwords

[Andy Eastham]

Shin,

I've never tried this, so it's pure speculation, but I believe all of the
grant information is contained in a regular table called user.

You should be able to copy this information into a temporary table using
select into, then perform regular updates to change the host information to
match your new subnets.  Then simply copy it back to the user table, which
will add these records to the existing records, crucially with the same
passwords.  You might have to repeat this a few times to get all the subnets
in.

I'd certainly perform lots of testing away from the live system before I
tried this for real.

Also, hopefully someone else will comment on whether this will actually
work, or whether their is a fatal flaw in the idea.

Best regards,

Andy


> -Original Message-
> From: Shin [mailto:[EMAIL PROTECTED]
> Sent: 30 September 2003 11:02
> To: [EMAIL PROTECTED]
> Subject: GRANT update query: Updating host access entry for users but
> retaining existing passwords
>
>
> Hi,
>
> I've got a MYSQL 3.23.x setup that has approx 4000 database and 4000
> user accounts. 1 database per user.
>
> I created each userid from a script of the form
>
> CREATE DATABASE mdb_userid;
> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,
> INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd';
> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,
> INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED]  IDENTIFIED BY 'passwd';
> FLUSH PRIVILEGES;
>
> (this is part of a much longer script that I pass thru to mysql and
> I'm using dummy/example userid,passwd and host entries in the above).
>
> I now need to grant additional access from a number of subnets for
> each of the users in the database to their own database. I wanted to
> use a statement of the form
>
> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
> ALTER,INDEX,RELOAD ON mdb_userid.* TO
> userid@'192.168.10.0/255.255.255.0' IDENTIFIED BY 'passwd';
>
> But the issue is that I do not have the plain text versions of each
> of the users password; as many have changed over the years. So I
> can't use the latter format with IDENTIFIED BY as I want to retain
> existing passwds.
>
> If I just do the above without having an IDENTIFIED BY entry then
> the users get a blank passwd which is definetly a no-no in my
> environment.
>
> Ideally what I'd like is a quick and simple way to grant users
> access from the new subnets but to retain their existing passwords -
> and without me having to extract all their existing encrypted
> passwords and then inserts them in afterwards in the appropriate
> tables - I prefer using GRANT.
>
> Is there anyway I can what I'm after?
>
> many thanks
> Shin
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

GRANT update query: Updating host access entry for users but retaining existing passwords

[Shin]

Hi,

I've got a MYSQL 3.23.x setup that has approx 4000 database and 4000
user accounts. 1 database per user.

I created each userid from a script of the form 

CREATE DATABASE mdb_userid;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON 
mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON 
mdb_userid.* TO [EMAIL PROTECTED]  IDENTIFIED BY 'passwd';
FLUSH PRIVILEGES;

(this is part of a much longer script that I pass thru to mysql and
I'm using dummy/example userid,passwd and host entries in the above).

I now need to grant additional access from a number of subnets for
each of the users in the database to their own database. I wanted to
use a statement of the form

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,INDEX,RELOAD ON mdb_userid.* 
TO userid@'192.168.10.0/255.255.255.0' IDENTIFIED BY 'passwd';

But the issue is that I do not have the plain text versions of each
of the users password; as many have changed over the years. So I
can't use the latter format with IDENTIFIED BY as I want to retain
existing passwds.

If I just do the above without having an IDENTIFIED BY entry then
the users get a blank passwd which is definetly a no-no in my
environment.

Ideally what I'd like is a quick and simple way to grant users
access from the new subnets but to retain their existing passwords -
and without me having to extract all their existing encrypted
passwords and then inserts them in afterwards in the appropriate
tables - I prefer using GRANT.

Is there anyway I can what I'm after?

many thanks
Shin


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Getting the primary key in an update query

[Daniel Rossi]

that works cheers

>>> "Roger Baklund" <[EMAIL PROTECTED]> 09/11/03 10:48am >>>
* Daniel Rossi
> Hi there i was wondering if there was anyway to determine the
> primary key field instead of explicitly setting it in an update
> sql query ?
>
> for instance i'd like to go update table *** where primarykey=1
> instead of where id=1 as the primary key is named differentrly
> for each table and i'm trying to create a global data manager,
> let me know thanks.

Since 3.23.11 you can use _rowid as an alias for an integer type unique
indexed column:

http://www.mysql.com/doc/en/News-3.23.11.html >

mysql> use test
Database changed
mysql> create table testrowid (x int not null primary key);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into testrowid values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testrowid where _rowid=2;
+---+
| x |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

--
Roger



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Getting the primary key in an update query

[Roger Baklund]

* Daniel Rossi
> Hi there i was wondering if there was anyway to determine the
> primary key field instead of explicitly setting it in an update
> sql query ?
>
> for instance i'd like to go update table *** where primarykey=1
> instead of where id=1 as the primary key is named differentrly
> for each table and i'm trying to create a global data manager,
> let me know thanks.

Since 3.23.11 you can use _rowid as an alias for an integer type unique
indexed column:

http://www.mysql.com/doc/en/News-3.23.11.html >

mysql> use test
Database changed
mysql> create table testrowid (x int not null primary key);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into testrowid values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testrowid where _rowid=2;
+---+
| x |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Getting the primary key in an update query

[Daniel Rossi]

Hi there i was wondering if there was anyway to determine the primary key field 
instead of explicitly setting it in an update sql query ?

for instance i'd like to go update table *** where primarykey=1 instead of where id=1 
as the primary key is named differentrly for each table and i'm trying to create a 
global data manager, let me know thanks.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Possible: Update query within another query's loop?

[Fred van Engen]

Antony,

On Thu, Aug 28, 2003 at 06:45:27PM +0400, Antony Dovgal wrote:
> On Thu, 28 Aug 2003 16:37:41 +0200
> Fred van Engen <[EMAIL PROTECTED]> wrote:
> 
> > Please elaborate.
> 
> I've already answered:
> 

On the list I just saw your 'bad advice' message without explanation.


> On Thu, 28 Aug 2003 17:07:19 +0400
> Antony Dovgal <[EMAIL PROTECTED]> wrote:
> 
> > You don't need execute UPDATE's in the loop in this case.
> 

Correct.


> and you don't need to make another one connection, of course.
> 

In the general case you may need to, but not in the usual case in which
you use mysql_query. I thought you did, but just double-checked. You
only need to if you use mysql_unbuffered_query and don't read all
results first.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Possible: Update query within another query's loop?

[Mark Richards]

Yes, that advice worked perfectly.  Now I understand.  

This is one very nice data storage system.  It's fast, and it can deal with
multiple pointers - separate sessions - into the same table without
conflict.  I guess it's up to the programmer to make certain that one
connection doesn't change data the other is dependent on :)

Thanks for the great idea.

-m-


-Original Message-
From: Antony Dovgal [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 28, 2003 10:30
To: [EMAIL PROTECTED]
Subject: Re: Possible: Update query within another query's loop?

On Thu, 28 Aug 2003 16:23:35 +0200
Fred van Engen <[EMAIL PROTECTED]> wrote:

> You need to make a separate connection to MySQL for the outer query to
> prevent the inner query from messing up the outer query's result set.
> See the PHP manual for obtaining the connection id's from mysql_connect
> and using them with other mysql functions.

ouch!
it's a kinda bad advice.

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Possible: Update query within another query's loop?

[Antony Dovgal]

On Thu, 28 Aug 2003 16:37:41 +0200
Fred van Engen <[EMAIL PROTECTED]> wrote:

> Please elaborate.

I've already answered:

On Thu, 28 Aug 2003 17:07:19 +0400
Antony Dovgal <[EMAIL PROTECTED]> wrote:

> You don't need execute UPDATE's in the loop in this case.
> Try  smthing like that:
>  .
> 
> $ids = Array();
> while ($row = mysql_fetch_assoc($result1))
> {
>  $ids[] = $row["id"];
> }
> 
> $ids_string = implode(',',$ids);
> 
> //of course, we need to check if $id_string is not empty
> 
> $q2 ="UPDATE  table  SET  review = 1  WHERE id IN (".$ids_string.")";
> 
> .
> ?>

there is no need to use nested loop at all.
just collect id's and execute 1(one) query, which will update all records.

and you don't need to make another one connection, of course.

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Possible: Update query within another query's loop?

[Fred van Engen]

Antony,

On Thu, Aug 28, 2003 at 06:29:54PM +0400, Antony Dovgal wrote:
> On Thu, 28 Aug 2003 16:23:35 +0200
> Fred van Engen <[EMAIL PROTECTED]> wrote:
> 
> > You need to make a separate connection to MySQL for the outer query to
> > prevent the inner query from messing up the outer query's result set.
> > See the PHP manual for obtaining the connection id's from mysql_connect
> > and using them with other mysql functions.
> 
> ouch!
> it's a kinda bad advice.
> 

Please elaborate.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Possible: Update query within another query's loop?

[Antony Dovgal]

On Thu, 28 Aug 2003 16:23:35 +0200
Fred van Engen <[EMAIL PROTECTED]> wrote:

> You need to make a separate connection to MySQL for the outer query to
> prevent the inner query from messing up the outer query's result set.
> See the PHP manual for obtaining the connection id's from mysql_connect
> and using them with other mysql functions.

ouch!
it's a kinda bad advice.

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Possible: Update query within another query's loop?

[Fred van Engen]

Mark,

On Thu, Aug 28, 2003 at 09:59:18AM -0400, Mark Richards wrote:
> I am still quite new to MySQL and have a basic question.  I am using PHP, so
> forgive me if this is more a PHP issue.
> 
> I want to perform an update to a specific record based on a condition.  In
> the outer loop, I have Query1, which returns set Result1.  Inside this loop,
> I run an UPDATE query which returns Result2.  
> 
> // executed first query.
> while ($row = mysql_fetch_assoc($result1))
>   {
> // get the record ID for the row we are on.
> $recid = $row["id"];
> // construct a new  query
> $q2 ="UPDATE  `table`  SET  `review` = 1  where id =
> '".$recid."';";
> $result2 = mysql_query($q2)
>   }
> 
> Is this a valid use of MySQL?  I am concerned that executing the update
> query might somehow effect the first one that is used in the outer loop.  
> 

You need to make a separate connection to MySQL for the outer query to
prevent the inner query from messing up the outer query's result set.
See the PHP manual for obtaining the connection id's from mysql_connect
and using them with other mysql functions.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Possible: Update query within another query's loop?

[Mark Richards]

I am still quite new to MySQL and have a basic question.  I am using PHP, so
forgive me if this is more a PHP issue.

I want to perform an update to a specific record based on a condition.  In
the outer loop, I have Query1, which returns set Result1.  Inside this loop,
I run an UPDATE query which returns Result2.  

// executed first query.
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row["id"];
// construct a new  query
$q2 ="UPDATE  `table`  SET  `review` = 1  where id =
'".$recid."';";
  $result2 = mysql_query($q2)
}

Is this a valid use of MySQL?  I am concerned that executing the update
query might somehow effect the first one that is used in the outer loop.  

Mark Richards




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Possible: Update query within another query's loop?

[Antony Dovgal]

On Thu, 28 Aug 2003 08:54:54 -0400
"Mark Richards" <[EMAIL PROTECTED]> wrote:

> I am still quite new to MySQL and have a basic question.  I am using PHP, so
> forgive me if this is more a PHP issue.
> 
> I want to perform an update to a specific record based on a condition.  In
> the outer loop, I have Query1, which returns set Result1.  Inside this loop,
> I run an UPDATE query which returns Result2.  
> 
> // executed first query...
> while ($row = mysql_fetch_assoc($result1))
>   {
> // get the record ID for the row we are on.
> $recid = $row["id"];
> // construct a new  query
> $q2 ="UPDATE  `table`  SET  `review` = 1  where id =
> '".$recid."';";
> $result2 = mysql_query($q2)
>   }

You don't need execute UPDATE's in the loop in this case.
Try  smthing like that:




--
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Possible: Update query within another query's loop?

[Mark Richards]

I am still quite new to MySQL and have a basic question.  I am using PHP, so
forgive me if this is more a PHP issue.

I want to perform an update to a specific record based on a condition.  In
the outer loop, I have Query1, which returns set Result1.  Inside this loop,
I run an UPDATE query which returns Result2.  

// executed first query...
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row["id"];
// construct a new  query
$q2 ="UPDATE  `table`  SET  `review` = 1  where id =
'".$recid."';";
  $result2 = mysql_query($q2)
}

Is this a valid use of MySQL?  I am concerned that executing the update
query might somehow effect the first one that is used in the outer loop.  

Mark Richards



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Possible: Update query within another query's loop?

[Mark Richards]

I am still quite new to MySQL and have a basic question.  I am using PHP, so
forgive me if this is more a PHP issue.

I want to perform an update to a specific record based on a condition.  In
the outer loop, I have Query1, which returns set Result1.  Inside this loop,
I run an UPDATE query which returns Result2.  

// executed first query.
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row["id"];
// construct a new  query
$q2 ="UPDATE  `table`  SET  `review` = 1  where id =
'".$recid."';";
  $result2 = mysql_query($q2)
}

Is this a valid use of MySQL?  I am concerned that executing the update
query might somehow effect the first one that is used in the outer loop.  

Mark Richards


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update query using inner join on same table

[Victoria Reznichenko]

"Jonathan Patton" <[EMAIL PROTECTED]> wrote:
> I have a query that runs in Microsoft Access against my mysql database just fine. It 
> is: 
> 
> 
> UPDATE discussion_categories AS discussion_categories_1 
> INNER JOIN discussion_categories ON 
> discussion_categories_1.parent_1_text = discussion_categories.name 
> SET discussion_categories_1.parent_1 = [discussion_categories].[category_id];
> 
> 
> I tried to write this in mysql but couldn't figure it out. Is it possible to join a 
> table on itself in mysql?

Yes, you can do it since version 4.0.4


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: update query using inner join on same table

[Jonathan Patton]

I answered my own question, this works for joining a table on itself and doing an 
update query:

update 
discussion_categories discussion_categories1, discussion_categories
set 
discussion_categories1.parent_1 = discussion_categories.category_id
where 
discussion_categories1.`parent_1_text` = discussion_categories.name



> -Original Message-
> From: Jonathan Patton 
> Sent: Wednesday, July 23, 2003 8:39 AM
> To: [EMAIL PROTECTED]
> Subject: update query using inner join on same table
> 
> 
> Hi, 
> 
> I have a query that runs in Microsoft Access against my mysql 
> database just fine. It is: 
> 
>  
> UPDATE discussion_categories AS discussion_categories_1 
> INNER JOIN discussion_categories ON 
> discussion_categories_1.parent_1_text = discussion_categories.name 
> SET discussion_categories_1.parent_1 = 
> [discussion_categories].[category_id];
> 
> 
> I tried to write this in mysql but couldn't figure it out. Is 
> it possible to join a table on itself in mysql?
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]