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

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

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

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: mysql@lists.mysql.com
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]

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: mysql@lists.mysql.com
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]

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: mysql@lists.mysql.com
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

[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 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]

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

[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]

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

[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]

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. list 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]

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]
 

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 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]

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

[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 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]

RE: update query question

[Chris W. Parker]

[EMAIL PROTECTED] mailto:[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 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]

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 with substring

[Paul DuBois]

At 11:26 +0200 5/30/03, Davy Obdam wrote:
Hello people,

I am trying to run this query:

UPDATE table1, table2 SET table1.periode = table.periode WHERE 
table1.id = 3 AND SUBSTRING_INDEX( table1.name, '.', - 1 ) = 
table2.name

But i keep getting the same error message

You have an error in your SQL syntax near '
table2 SET table1.periode = table2.periode WHERE table1.id = 3 AND 
SUBSTRIN' at line 1

Is it possible to use a SUBSTRING_INDEX in a update query, or what i 
am i doing wrong?
That's not what the error message is telling you.  It's complaining about
the second table name prior to the SET keyword.  My guess is that your
version of MySQL is not recent enough to support multiple-table updates
(which were implemented around 4.0.2 or so).  What version is your server?
Any help is appreciated, thanks for your time

Best regards,

Davy Obdam

--
---
Davy Obdam Web application developer
Networking4all
email: [EMAIL PROTECTED]
email: [EMAIL PROTECTED]
internet: http://www.networking4all.com
---


--
Are you MySQL certified?, http://www.mysql.com/certification/
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Mr. Paul DuBois [EMAIL PROTECTED]
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Senior Technical Writer
 /_/  /_/\_, /___/\___\_\___/   Madison, Wisconsin, USA
___/   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 with substring

[jbonnett]

You can't have two tables in the UPDATE query, at least not in v3.x of
MySQL. I'm not sure about version 4. That's why it's complaining about
table2. I think the SUBSTRING_INDEX should be OK.

You may have to split your query into a series of queries. There is some
guidance in the MySQL manual.

John Bonnett

-Original Message-
From: Davy Obdam [mailto:[EMAIL PROTECTED]
Sent: Friday, 30 May 2003 6:57 PM
To: MySQL; MySQL-WIN
Subject: Update query with substring


Hello people,

I am trying to run this query:

UPDATE table1, table2 SET table1.periode = table.periode WHERE table1.id 
= 3 AND SUBSTRING_INDEX( table1.name, '.', - 1 ) = table2.name

But i keep getting the same error message

You have an error in your SQL syntax near '
table2 SET table1.periode = table2.periode WHERE table1.id = 3 AND 
SUBSTRIN' at line 1

Is it possible to use a SUBSTRING_INDEX in a update query, or what i am 
i doing wrong?

Any help is appreciated, thanks for your time

Best regards,

Davy Obdam

-- 
---
Davy Obdam 
Web application developer

Networking4all
email: [EMAIL PROTECTED]
email: [EMAIL PROTECTED]
internet: http://www.networking4all.com
---




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

Re: Update query with substring

[Victoria Reznichenko]

Davy Obdam [EMAIL PROTECTED] wrote:
 I am trying to run this query:
 
 UPDATE table1, table2 SET table1.periode = table.periode WHERE table1.id 
 = 3 AND SUBSTRING_INDEX( table1.name, '.', - 1 ) = table2.name
 
 But i keep getting the same error message
 
 You have an error in your SQL syntax near '
 table2 SET table1.periode = table2.periode WHERE table1.id = 3 AND 
 SUBSTRIN' at line 1
 
 Is it possible to use a SUBSTRING_INDEX in a update query, or what i am 
 i doing wrong?
 

What version of MySQL server do you use? Multi-table updates is supported only since 
4.0.4 version.


-- 
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 doesn't work at a PHP form

[Fred van Engen]

Hi,

On Wed, Apr 02, 2003 at 05:01:48PM -0300, Sibusy wrote:
 I'm trying to perform UPDATE with a PHP form , but the UPDATE query doesn't work 
 anyway, returningCan't perform the update, according to code below. It doesn't 
 return any error at PHP nor at Mysql, I have tested the variables and they are 
 beeing sent properly.Could someone help me???
 Here goes the code : ?php 
 $db=mysql_connect($host,$user,$senha) or die(Erro de Conexão);
 
 mysql_select_db($dbnome,$db);
 
 $sql=UPDATE [LOW PRIORITY] grupoadministracao WHERE idgrupo = '$nid' SET nome = 
 '$nome', resp= '$resp',  
  cnpj='$cnpj', cpf='$cpf', ie='$ie', 
 endereco='$endereco',numero='$numero',cpto='$cpto', cep='$cep',  
  
 telefone1='$telefone1',telefone2='$telefone2',fax='$fax',celular='$celular',email='$email',login='$login',senha='$senha';
  
 

The WHERE should be at the end of the query.

Also use addslashes() to properly escape quotes in your variables.


 $result=mysql_query($sql,$db);

  if($result) {
  echo (Datab$nome/b, registro nºb$nid/b was 
 successfully updated.br);
  } else {
 echo Can't perform the update. ;
   mysql_error();

Try this:

echo mysql_error()


  }
 


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: Update query with Join

[Egor Egorov]

On Monday 10 March 2003 10:40, Hu Qinan wrote:

 Which records in tbl1 are to be updated are determined by an INNER JOIN
 with tbl2. I have tried the following:

 UPDATE tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id
 SET tbl1.col1 = 0;

 UPDATE tbl1, tbl2
 SET tbl1.col1 = 0
 WHERE tbl1.id = tbl2.id;

 UPDATE tbl1
 SET tbl1.col1 = 0
 INNER JOIN tbl2 ON tbl1.id = tbl2.id;

 But none of the above codes work. How to write this query?

Multi-table updates are supported only since 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/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Update Query problem

[Stefan Hinz, iConnect \(Berlin\)]

Dear Amit,

 update lotjobtemp set duedate =  (select duedate from
 importparameters);

In MySQL, you need 2 queries for this:

SELECT @var:=duedate FROM importparameters;
UPDATE lotjobtemp SET duedate = @var;

Most probably, you will want to use a WHERE clause for both statments.

To make this transaction safe, you can use InnoDB tables. Issue BEGIN
before the SELECT statement and COMMIT after the UPDATE statement.

HTH!
--
  Stefan Hinz [EMAIL PROTECTED]
  Geschäftsführer / CEO iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3


- Original Message -
From: Amit Lonkar [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Tuesday, December 10, 2002 1:34 PM
Subject: Update Query problem


 Hi All!!

 I am writing a .sql file. I want to execute this file
 at runtime.

 One of the queries is :-
 update lotjobtemp set duedate =  (select duedate from
 importparameters);

 This query generates an error as
 ERROR 1064: You have an error in your SQL syntax near
 'select duedate from impor
 tparameters' at line 1.

 I guess this is because subselects fail in mysql. Can
 any one tell me how to overcome this problem.

 Regards
 Amit Lonkar


 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.yahoo.com

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: update query confusion

[Jay Blanchard]

{snip]
I can't work out why this query is also updating a TIMESTAMP col?

UPDATE news SET title = 'new title', text = 'new text' WHERE id = '4'
[/snip]

Because, according to TFM; (http://www.mysql.com/doc/D/A/DATETIME.html)

Automatic updating of the first TIMESTAMP column occurs under any of the
following conditions:

*The column is not specified explicitly in an INSERT or LOAD DATA INFILE
statement.
*The column is not specified explicitly in an UPDATE statement and some
other column changes value. (Note that an UPDATE that sets a column to the
value it already has will not cause the TIMESTAMP column to be updated,
because if you set a column to its current value, MySQL ignores the update
for efficiency.)
*You explicitly set the TIMESTAMP column to NULL.

HTH!

Jay



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: update query confusion

[Nick Wilson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


* and then Jay Blanchard declared
 other column changes value. (Note that an UPDATE that sets a column to the
 value it already has will not cause the TIMESTAMP column to be updated,
 because if you set a column to its current value, MySQL ignores the update
 for efficiency.)
 *You explicitly set the TIMESTAMP column to NULL.
 
 HTH!

Sure does, cheers Jay.
- -- 
Nick Wilson //  www.explodingnet.com



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.6 (GNU/Linux)

iD8DBQE9CfkZHpvrrTa6L5oRAlgCAJ47bPK5shotAZek3a8p7Fw8ZqoyFACgq8JV
3XjDcGjY6BgrcWc0zKg35Bo=
=qTFK
-END PGP SIGNATURE-

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: update query confusion

[Egor Egorov]

Nick,
Friday, June 14, 2002, 3:40:23 PM, you wrote:

NW I can't work out why this query is also updating a TIMESTAMP col?

It's a paticular feature of TIMESTAMP column. Read the manual:
 http://www.mysql.com/doc/D/A/DATETIME.html

NW UPDATE news SET title = 'new title', text = 'new text' WHERE id = '4' 

NW The table is very simple:

NW *   id INT
NW *   date TIMESTAMP // this is current date when  'updating'?
NW *   title VARCHAR
NW *   text TEXT

NW I need the date col to remain the same, what am I doing wrong?

NW Much thanks...





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



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: update query fails

[Ryan Hatch]

be aware... linux/unix table names are case sensitive.  if you're using Win32 MySQL
on your system, the query might work, but on the ISP side, if it's a *NIX system...
you must have the correct capitalization.

moreover, if any of your variables contain a single quote character ( ' ), it will
screw up your SQL statement.  make sure you run all your variables through a filter
to check for single quotes.   either backslash them out ( \ ) or prevent your users
from using them.

-Ryan Hatch

Sven Bentlage wrote:

 Hi everyone!

 Are there any limitations on update queries, like on how many fields one
 can update at the same time?

 The first  update query works just fine, the second does not work at all:

 working :
  update memberscopy set password='$password' where name =
 '$f_name' and surname ='$f_surname' 

 not working:
  update memberscopy
 set rank='$rank',
 cname='$cname',
 caddress='$caddress',
 ctel='$ctel',
 cfax='$cfax',
 cmobile='$cmobile',
 cemail='$cemail',
 curl='$curl',
 btype='$btype',
 hq='$hq',
 quali='$quali',
 experi='$experi',
 inhouse='$inhouse',
 resid='$resid',
 ptel='$ptel',
 pfax='$pfax',
 pmobile='$pmobile',
 pemail='$pemail',
 marital='$marital',
 spouse='$spouse',
 children_number='$children_number',
 children_names='$children_names',
 hobbies='$hobbies',
 membership='$membership',
 pcont='$pcont',
 lastup=now()
 where name = '$f_name' and surname = '$f_surname'  

 The 2nd query works (for some fields only) on my local machine, but not
 on the ISP's server

 Thanks for your help.

 Sven

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: update query produces warnings?(fixed)

[Tyler Longren]

This post can be ignored now.  Turned out the datatype for the id field was set to 
tinyint, changed it to int and everything worked great.

Tyler

On Thu, 31 May 2001 00:26:12 -0500
Tyler Longren [EMAIL PROTECTED] wrote:

 Hello everyone,
 
 I have a database of alumni at school.  Each alumnus that registers gets assigned 
their own id.  Id's are made with mysql's auto_increment.  Everything worked fine 
until a few days ago when everybody that signed up got the id of 127 assigned to 
them.  MySQL will NOT assign them an id higher than 127.  So, now everybody that 
signs up gets the id of 127.  I tried to fix this by using update on the table...here 
is an example of the table:
 
 ID | fname | lname
 ---
 127 | MaryBeth | Elliott
 ---
 127 | Becky | Beving
 ---
 127 | Russell | Oswalt
 ---
 127 | Wanda | Oswalt
 ---
 127 | Brandon | Kohlwes
 ---
 127 | Kenneth | Smith, Jr.
 ---
 127 | Benjamin | Dykstra
 ---
 127 | Laura | Hand
 ---
 126 | Eric | Beal
 ---
 125 | Doug | Franklin
 ---
 
 Here is what I used to try to UPDATE the id:
 UPDATE alumni SET id=128 WHERE fname='Benjamin' AND lname='Dykstra';
 That didn't work, it said that 0 lines were changed and there was one warning 
(couldn't figure out how to read the warning).
 
 I also tried this:
 UPDATE alumni SET id=id+1 WHERE fname='Benjamin' AND lname='Dykstra';
 That also didn't work...same result as above.
 
 I am convinced that my syntax is correct because phpMyAdmin can't even change the 
id.  Anybody have any ideas why this happened?  BTW, I'm running mysql-3.23.38 on NT4.
 
 Thanks everyone,
 -- 
 Tyler Longren
 [EMAIL PROTECTED]
 Currently Unemployed
 www.noworkfortyler.com
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: update query produces warnings?

[Chris Bolt]

Change the id column to something larger than a TINYINT (like MEDIUMINT or
INT). You should also make ID the primary key.

 Hello everyone,

 I have a database of alumni at school.  Each alumnus that
 registers gets assigned their own id.  Id's are made with mysql's
 auto_increment.  Everything worked fine until a few days ago when
 everybody that signed up got the id of 127 assigned to them.
 MySQL will NOT assign them an id higher than 127.  So, now
 everybody that signs up gets the id of 127.  I tried to fix this
 by using update on the table...here is an example of the table:

 ID | fname | lname
 ---
 127 | MaryBeth | Elliott
 ---
 127 | Becky | Beving
 ---
 127 | Russell | Oswalt
 ---
 127 | Wanda | Oswalt
 ---
 127 | Brandon | Kohlwes
 ---
 127 | Kenneth | Smith, Jr.
 ---
 127 | Benjamin | Dykstra
 ---
 127 | Laura | Hand
 ---
 126 | Eric | Beal
 ---
 125 | Doug | Franklin
 ---

 Here is what I used to try to UPDATE the id:
 UPDATE alumni SET id=128 WHERE fname='Benjamin' AND lname='Dykstra';
 That didn't work, it said that 0 lines were changed and there was
 one warning (couldn't figure out how to read the warning).

 I also tried this:
 UPDATE alumni SET id=id+1 WHERE fname='Benjamin' AND lname='Dykstra';
 That also didn't work...same result as above.

 I am convinced that my syntax is correct because phpMyAdmin can't
 even change the id.  Anybody have any ideas why this happened?
 BTW, I'm running mysql-3.23.38 on NT4.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: UPDATE query with ORDER BY and LIMIT

[Carsten H. Pedersen]

 Hi to all!
 
 I have to use an UPDATE query with ORDER BY and LIMIT clauses.
 This is the query:
 
 UPDATE TBLTEST SET LOCKEDBY='test' WHERE FIELD1 LIKE 'test_' ORDER BY 
 INS_DATE LIMIT 1;
 
 MySql tells me that I have an error in my SQL syntax near 'ORDER BY 
 INS_DATE' at line 1.
 I use Mysql 3.23.30-gamma with MyIsam table format.
 I have read the manual and the syntax, according to that, seems to be 
 correct... but... nothing is as it seems What is wrong?

Then you need to go back to reading the manual again. UPDATE
statements do not allow for ORDER BY clauses.

/ Carsten

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: UPDATE query with ORDER BY and LIMIT

[Denis Gasparin]

I see the definition of update in the online manual... This is the link:
http://www.mysql.com/doc/U/P/UPDATE.html

and this is what is written:

 From MySQL manual ONLINE ***

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
 SET col_name1=expr1, [col_name2=expr2, ...]
 [WHERE where_definition]
 [ORDER BY ...]
 [LIMIT #]

UPDATE updates columns in existing table rows with new values. The SET 
clause indicates which columns to modify and the values they should be 
given. The WHERE clause, if given, specifies which rows should be updated. 
Otherwise all rows are updated. If the ORDER BY clause is specified, the 
rows will be updated in the order that is specified.

 End From MySQL manual ONLINE ***

So i read the manual... but is the manual wrong?

Denis



At 10.52 21/02/01, Carsten H. Pedersen wrote:
  Hi to all!
 
  I have to use an UPDATE query with ORDER BY and LIMIT clauses.
  This is the query:
 
  UPDATE TBLTEST SET LOCKEDBY='test' WHERE FIELD1 LIKE 'test_' ORDER BY
  INS_DATE LIMIT 1;
 
  MySql tells me that I have an error in my SQL syntax near 'ORDER BY
  INS_DATE' at line 1.
  I use Mysql 3.23.30-gamma with MyIsam table format.
  I have read the manual and the syntax, according to that, seems to be
  correct... but... nothing is as it seems What is wrong?

Then you need to go back to reading the manual again. UPDATE
statements do not allow for ORDER BY clauses.

/ Carsten


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: UPDATE query with ORDER BY and LIMIT

[Carsten H. Pedersen]

I stand corrected - sorta. 

Look further down in the manual: ORDER BY for UPDATE
is not introduced until v. 4.0.0.

/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq

 -Original Message-
 From: Denis Gasparin [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, February 21, 2001 11:05 AM
 To: Carsten H. Pedersen; [EMAIL PROTECTED]
 Subject: RE: UPDATE query with ORDER BY and LIMIT
 
 
 
 
 I see the definition of update in the online manual... This is the link:
 http://www.mysql.com/doc/U/P/UPDATE.html
 
 and this is what is written:
 
  From MySQL manual ONLINE ***
 
 UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
  SET col_name1=expr1, [col_name2=expr2, ...]
  [WHERE where_definition]
  [ORDER BY ...]
  [LIMIT #]
 
 UPDATE updates columns in existing table rows with new values. The SET 
 clause indicates which columns to modify and the values they should be 
 given. The WHERE clause, if given, specifies which rows should be 
 updated. 
 Otherwise all rows are updated. If the ORDER BY clause is specified, the 
 rows will be updated in the order that is specified.
 
  End From MySQL manual ONLINE ***
 
 So i read the manual... but is the manual wrong?
 
 Denis
 
 
 
 At 10.52 21/02/01, Carsten H. Pedersen wrote:
   Hi to all!
  
   I have to use an UPDATE query with ORDER BY and LIMIT clauses.
   This is the query:
  
   UPDATE TBLTEST SET LOCKEDBY='test' WHERE FIELD1 LIKE 'test_' ORDER BY
   INS_DATE LIMIT 1;
  
   MySql tells me that I have an error in my SQL syntax near 'ORDER BY
   INS_DATE' at line 1.
   I use Mysql 3.23.30-gamma with MyIsam table format.
   I have read the manual and the syntax, according to that, seems to be
   correct... but... nothing is as it seems What is wrong?
 
 Then you need to go back to reading the manual again. UPDATE
 statements do not allow for ORDER BY clauses.
 
 / Carsten
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail 
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php