Weird UPDATE Problem
Hello all. I have recently finished my migration from an older server to a newer server running RHEL 6. The MySQL version went from 5.0.77 to 5.1.52. In my application, this query used to work just fine: $paid_query = mysql_query(UPDATE $table_name SET owed = 0 WHERE s_id = $student); Where table_name was mysql_real_escape_string(collection_41_students). With the new MySQL version, the UPDATE query does not work, and echo mysql_error(); results nothing. Is my syntax correct going from version to version? Thanks in advance, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Weird UPDATE Problem
That query looks fine. What error are you getting if you execute the query from the CLI? Also is it possible that the s_id or owed columns are no longer numeric data types? If this column(s) is/are a character type now, then you would need to have the values in quotes. -Brandon On 10/10/2011 09:14 AM, D. Marshall Lemcoe Jr. wrote: Hello all. I have recently finished my migration from an older server to a newer server running RHEL 6. The MySQL version went from 5.0.77 to 5.1.52. In my application, this query used to work just fine: $paid_query = mysql_query(UPDATE $table_name SET owed = 0 WHERE s_id = $student); Where table_name was mysql_real_escape_string(collection_41_students). With the new MySQL version, the UPDATE query does not work, and echo mysql_error(); results nothing. Is my syntax correct going from version to version? Thanks in advance, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Weird UPDATE Problem
2011/10/10 09:19 -0400, Brandon Phelps If this column(s) is/are a character type now, then you would need to have the values in quotes. Note that because of implicit conversion if they had numberic values no error would be reported, but maybe the equality would not be exact. -- 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 Problem when ORing w/ Long.MIN_VALUE
For the curious: As usual select is not broken. Lesson learned: Always watch out for warnings: http://bugs.mysql.com/bug.php?id=41007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Problem when ORing w/ Long.MIN_VALUE
Hi Gautam nope yours is not a bug. That's all fine. Hex numbers are 64 bit unsigned. So for -1 you have to insert cast(0x as signed). Cheers, Daniel Hi Daniel, I can see the problem without using update. However, I am a newbie at mysql, so can't say for certain if it's a bug: mysql drop table if exists foo; mysql create table foo (id int signed, val bigint signed); mysql insert into foo values (0x, 0x), (-1, -1); mysql select hex(id), hex(val) from foo; +--+--+ | hex(id) | hex(val) | +--+--+ | 7FFF | 7FFF | | | | +--+--+ 2 rows in set (0.00 sec) Regards Gautam Daniel Doubleday wrote: Hi everybody - I'm experiencing some really weird update behaviour (mysql 5.0) when or'ing results from subselects using Long.MIN_VALUE. But before I post a bug report I wanted to ask if I'm missing something. drop table if exists foo; drop table if exists bar; create table foo (fooid int, fooval bigint); create table bar (barid int, barval bigint); insert into foo values (1, null), (2, null); insert into bar values (1, 123), (2, 345); update foo set fooval = (select barval from bar where barid = fooid) | 0x8000; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit logic result is always unsigned bigint) # Same thing when you replace subselect by multi table update syntax update foo, bar set fooval = barval | 0x8000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # and it seems that its all about MSB sign bit, cause thats fine: update foo, bar set fooval = barval | 0x7000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 8070450532247928955 | # | 2 | 8070450532247929177 | # +---+-+ # 2 rows in set (0.00 sec) # and casting the or result! does the trick too though I dont understand why ... update foo set fooval = cast((select barval from bar where barid = fooid) | 0x8000 as signed); select * from foo; # +---+--+ # | fooid | fooval | # +---+--+ # | 1 | -9223372036854775685 | # | 2 | -9223372036854775463 | # +---+--+ # 2 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]
Update Problem when ORing w/ Long.MIN_VALUE
Hi everybody - I'm experiencing some really weird update behaviour (mysql 5.0) when or'ing results from subselects using Long.MIN_VALUE. But before I post a bug report I wanted to ask if I'm missing something. drop table if exists foo; drop table if exists bar; create table foo (fooid int, fooval bigint); create table bar (barid int, barval bigint); insert into foo values (1, null), (2, null); insert into bar values (1, 123), (2, 345); update foo set fooval = (select barval from bar where barid = fooid) | 0x8000; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit logic result is always unsigned bigint) # Same thing when you replace subselect by multi table update syntax update foo, bar set fooval = barval | 0x8000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # and it seems that its all about MSB sign bit, cause thats fine: update foo, bar set fooval = barval | 0x7000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 8070450532247928955 | # | 2 | 8070450532247929177 | # +---+-+ # 2 rows in set (0.00 sec) # and casting the or result! does the trick too though I dont understand why ... update foo set fooval = cast((select barval from bar where barid = fooid) | 0x8000 as signed); select * from foo; # +---+--+ # | fooid | fooval | # +---+--+ # | 1 | -9223372036854775685 | # | 2 | -9223372036854775463 | # +---+--+ # 2 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: Update Problem when ORing w/ Long.MIN_VALUE
Hi Daniel, I can see the problem without using update. However, I am a newbie at mysql, so can't say for certain if it's a bug: mysql drop table if exists foo; mysql create table foo (id int signed, val bigint signed); mysql insert into foo values (0x, 0x), (-1, -1); mysql select hex(id), hex(val) from foo; +--+--+ | hex(id) | hex(val) | +--+--+ | 7FFF | 7FFF | | | | +--+--+ 2 rows in set (0.00 sec) Regards Gautam Daniel Doubleday wrote: Hi everybody - I'm experiencing some really weird update behaviour (mysql 5.0) when or'ing results from subselects using Long.MIN_VALUE. But before I post a bug report I wanted to ask if I'm missing something. drop table if exists foo; drop table if exists bar; create table foo (fooid int, fooval bigint); create table bar (barid int, barval bigint); insert into foo values (1, null), (2, null); insert into bar values (1, 123), (2, 345); update foo set fooval = (select barval from bar where barid = fooid) | 0x8000; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit logic result is always unsigned bigint) # Same thing when you replace subselect by multi table update syntax update foo, bar set fooval = barval | 0x8000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 9223372036854775807 | # | 2 | 9223372036854775807 | # +---+-+ # 2 rows in set (0.00 sec) # and it seems that its all about MSB sign bit, cause thats fine: update foo, bar set fooval = barval | 0x7000 where fooid = barid; select * from foo; # +---+-+ # | fooid | fooval | # +---+-+ # | 1 | 8070450532247928955 | # | 2 | 8070450532247929177 | # +---+-+ # 2 rows in set (0.00 sec) # and casting the or result! does the trick too though I dont understand why ... update foo set fooval = cast((select barval from bar where barid = fooid) | 0x8000 as signed); select * from foo; # +---+--+ # | fooid | fooval | # +---+--+ # | 1 | -9223372036854775685 | # | 2 | -9223372036854775463 | # +---+--+ # 2 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]
Interresting update problem
Hi all, I'm trying to wrap my brain around folowing problem. table main(code, field_1, field_2, field_3, , , field_51) 111, 'X', '', 'X',,, 222, '', '', 'X',,, 333, '', 'X', '' ,,, 444, '', '', '' ,,, 555, 'X','X', '' ,,, table map(id, field) 1, 'field_1' 5, 'field_2' 9, 'field_3' 86, 'field_51' The exercise is: replace 'X' with map.id in main.map.field main.code and map.id are primary keys, all other are varchar. Hmm, did that make any sense? -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interresting update problem
What I've done in situations like this is write SQL that generates the SQL I
need. I then pipe out to a file and pipe it back in, or just pipe from one
instance of mysql to another.
Example SQL:
SELECT
CONCAT('UPDATE main SET ', field, ' = ', id, ';')
AS update_sql
FROM map
Example command to accomplish on one step:
mysql -u user -ppass -D db -e the above sql | mysql -u user -ppass -D db
HTH,
Dan
On 6/27/07, Mogens Melander [EMAIL PROTECTED] wrote:
Hi all,
I'm trying to wrap my brain around folowing problem.
table main(code, field_1, field_2, field_3, , , field_51)
111, 'X', '', 'X',,,
222, '', '', 'X',,,
333, '', 'X', '' ,,,
444, '', '', '' ,,,
555, 'X','X', '' ,,,
table map(id, field)
1, 'field_1'
5, 'field_2'
9, 'field_3'
86, 'field_51'
The exercise is: replace 'X' with map.id in main.map.field
main.code and map.id are primary keys, all other are varchar.
Hmm, did that make any sense?
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update problem with timestamp columns
Hi, I have a table with a time stamp column defined as ts timestamp(14) NOT NULL this is the first timestamp value in a series of timestamp columns. Whenever I update a row in the table ts gets updated with the current timestamp. I read from the documentaion that the first timestamp column in a create statement with neither DEFAULT nor ON UPDATE clauses is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. To solve the problem I try to alter the table with the following command: ALTER TABLE `test` CHANGE `ts` `ts` TIMESTAMP( 14 ) NOT NULL DEFAULT 'CURRENT_TIMESTAMP()' but whenever I update a row after running the command above ts still gets updated. Any ideas how I can restrict mysql to not update the ts value on update. BTW, Iam testing this on 3.23 but it has the same effect on 4.1 as well Regards, Danish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: update problem with timestamp columns
Did you successfully alter the table? What does SHOW CREATE TABLE give you? mysql CREATE TABLE test ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ts_modify TIMESTAMP ); Query OK, 0 rows affected (0.00 sec) mysql insert into test(id) VALUES();insert into test(id) VALUES();insert into test(id) VALUES();insert into test(id) VALUES(); Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) (the warnings are that the '' for id was truncated and the auto_increment was used) mysql select * from test; ++-+-+ | id | ts_create | ts_modify | ++-+-+ | 1 | 2006-05-19 11:18:07 | -00-00 00:00:00 | | 2 | 2006-05-19 11:18:24 | -00-00 00:00:00 | | 3 | 2006-05-19 11:18:25 | -00-00 00:00:00 | | 4 | 2006-05-19 11:18:25 | -00-00 00:00:00 | ++-+-+ 4 rows in set (0.00 sec) and then I update to see if it changes the timestamp at ts_create: mysql update test set ts_modify=DATE_SUB(NOW(),INTERVAL 6 MONTH); Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql select * from test; ++-+-+ | id | ts_create | ts_modify | ++-+-+ | 1 | 2006-05-19 11:18:07 | 2005-11-19 11:18:59 | | 2 | 2006-05-19 11:18:24 | 2005-11-19 11:18:59 | | 3 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 | | 4 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 | ++-+-+ 4 rows in set (0.00 sec) So you see, it worked -- it did not update the ts_create timestamp. I would guess your alter table failed. -Sheeri On 5/18/06, Danish [EMAIL PROTECTED] wrote: Hi, I have a table with a time stamp column defined as ts timestamp(14) NOT NULL this is the first timestamp value in a series of timestamp columns. Whenever I update a row in the table ts gets updated with the current timestamp. I read from the documentaion that the first timestamp column in a create statement with neither DEFAULT nor ON UPDATE clauses is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. To solve the problem I try to alter the table with the following command: ALTER TABLE `test` CHANGE `ts` `ts` TIMESTAMP( 14 ) NOT NULL DEFAULT 'CURRENT_TIMESTAMP()' but whenever I update a row after running the command above ts still gets updated. Any ideas how I can restrict mysql to not update the ts value on update. BTW, Iam testing this on 3.23 but it has the same effect on 4.1 as well Regards, Danish -- 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: sporadic batch update problem
Hi Jeff, all!
Jeff Drew wrote:
Sporadically, the last few entries of a batch are not written. I'm writing
to a mysql database using JDBC. Here's a short version of my code. Does
anyone have suggestions on possible causes or other diagnostics?
I do not claim any JBDC knowledge, so I have to speculate:
class DatabaseWriter{
int writeCount=0;
public DatabaseWriter(){
PreparedStatement preparedStatement = connection.prepareStatement(insert
into msgpersecond ( time , count , sendercompid , targetcompid )
values ( ? , ? , ? , ? ));
connection.setAutoCommit( false ); // turn off auto-Commit
}
public void process(Object input){
preparedStatement.setFloat( 2 , event.msgPerSecond );
preparedStatement.addBatch( );
writeCount++:
if (writeCount 50) {
updateCounts = preparedStatement.executeBatch( );
connection.commit( );
preparedStatement.clearBatch( );
writeCount=0;
}
}
}
process() gets called a lot. The code usually works fine, but sometimes 3
to 20 or so records that definitely are added to the batch but don't get
written.
I'd greatly appreciate any suggestions.
I do not see any handling of the records processed in the last, not
completed lot of 50. Do you call a final commit somewhere else?
Jörg
--
Joerg Bruehe, Senior Production Engineer
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]
sporadic batch update problem
Sporadically, the last few entries of a batch are not written. I'm writing
to a mysql database using JDBC. Here's a short version of my code. Does
anyone have suggestions on possible causes or other diagnostics?
class DatabaseWriter{
int writeCount=0;
public DatabaseWriter(){
PreparedStatement preparedStatement = connection.prepareStatement(insert
into msgpersecond ( time , count , sendercompid , targetcompid )
values ( ? , ? , ? , ? ));
connection.setAutoCommit( false ); // turn off auto-Commit
}
public void process(Object input){
preparedStatement.setFloat( 2 , event.msgPerSecond );
preparedStatement.addBatch( );
writeCount++:
if (writeCount 50) {
updateCounts = preparedStatement.executeBatch( );
connection.commit( );
preparedStatement.clearBatch( );
writeCount=0;
}
}
}
process() gets called a lot. The code usually works fine, but sometimes 3
to 20 or so records that definitely are added to the batch but don't get
written.
I'd greatly appreciate any suggestions.
Thanks
Update problem
Hi All, I'm working on a guestbook and have a problem with updating a table with php.. in the guestbook entry, i have a textarea that allows users to enter their comments, in multiple lines. however, once i get it out to prepare to put into sql database, it becomes a single line. i know this doesn't concern mysql but i was wondering: if we can create a column with TEXT field, how do we input entries that mysql actually input as multiple line? e.g: insert into mytable (myname, mymsg) values (will, my message); IF mymsg is a multi-line, how do i do it? many thanks in advance. will Yahoo! Mobile - Download the latest ringtones, games, and more!
update problem with mysqlimport (bug/misuse?)
Hi people I'm facing a (not huge) problem with mysqlimport. The mysql version I'm using is MySQL 4.0.11a-gamma'-Max' For updating a #29000 records table from fixed-lenght ASCII file, I'm using a php script that gets a record and executes and UPDATE for each one: pretty SLOW. SO I read carefully mysqlimport and think that is THE solution for the speed matter. BUT the fields that I don't include in the mysqlimport field list get EMPTY! Here are the details: This is the table: CREATE TABLE alfabeta ( id mediumint(5) unsigned NOT NULL default '0', nombre varchar(30) NOT NULL default '', presenta varchar(30) NOT NULL default '', precio float(7,2) unsigned NOT NULL default '0.00', precio_ponderado float(7,2) unsigned NOT NULL default '0.00', fecha_mod date NOT NULL default '-00-00', id_psico tinyint(1) NOT NULL default '0', anexo tinyint(1) unsigned NOT NULL default '0', pami char(1) NOT NULL default '0', id_laboratorio smallint(4) unsigned NOT NULL default '0', baja tinyint(1) unsigned NOT NULL default '0', heladera tinyint(1) unsigned NOT NULL default '0', id_troquel int(10) unsigned NOT NULL default '0', id_monodroga mediumint(6) unsigned NOT NULL default '0', id_acfa smallint(5) unsigned NOT NULL default '0', id_codbarra bigint(13) unsigned NOT NULL default '0', unidades smallint(4) unsigned NOT NULL default '0', importado tinyint(1) unsigned NOT NULL default '0', sifar char(1) NOT NULL default , id_tamano smallint(2) unsigned NOT NULL default '0', id_tipounid smallint(4) unsigned NOT NULL default '0', id_tipovta tinyint(1) NOT NULL default '0', id_acciofar int(2) NOT NULL default '0', PRIMARY KEY (id), KEY id_troquel (id_troquel), KEY nombre (nombre,presenta) ) TYPE=MyISAM; The mysqlimport command: mysqlimport osdata -u owner -pofthedata --debug -c id,nombre,presenta,precio,id_psico,pami,id_laboratorio,baja,heladera,id_troquel,id_codbarra,unidades,importado,sifar,id_tamano,id_tipovta -r -v -l alfabeta.csv Note: the original file alfabeta.txt have fixed-lenght fields, I use an awk script to convert to alfabeta.csv The question is that, the field precio_ponderado, for example, comes from another source, NOT from alfabeta.csv, so I load it from another .csv in another .php script. But after the execution of the mysqlimport, it gets 0.00, when, well, I expect that mysqlimport leaves it unchanged because I don't list it in the -c clause. So, is that a bug or mysqlimport is only useful for populating empty tables and for that reason emptyes the values of the columns not listed? If so, I'll must to discard that sooo elegant solution and use a .php script to make a HUGE .sql file with UPDATE's (IdontwantitIdontwantitIdontwantit!) thanks in advance Jorge Llarens ___ 100mb gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update problem with mysqlimport (bug/misuse?)
At 12:03 -0300 7/5/04, j llarens wrote: Hi people I'm facing a (not huge) problem with mysqlimport. The mysql version I'm using is MySQL 4.0.11a-gamma'-Max' For updating a #29000 records table from fixed-lenght ASCII file, I'm using a php script that gets a record and executes and UPDATE for each one: pretty SLOW. SO I read carefully mysqlimport and think that is THE solution for the speed matter. BUT the fields that I don't include in the mysqlimport field list get EMPTY! mysqlimport is for adding new records (or replacing existing ones). It does not update existing records. -- 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]
Update problem
I am trying to update a table from results generated from a select query,
but can't seem to get the queries running right. The select query works
fine, but when I try to use the results for an update it error out. Here is
my query:
db_connect();
$query = SELECT cst_SiteID FROM customers WHERE cst_Offer = 1;
echo $query;
echo br/;
$result = mysql_query($query);
$numofrows = mysql_num_rows($result);
$numofcols = mysql_num_fields($result);
echo table\n;
for ( $r = 0; $r $numofrows; $r++)
{
$row = mysql_fetch_array($result);
echo td$row[0]/td;
$updatequery = UPDATE assets .
SET asset_Managed = '2' .
WHERE asset_SiteID = $row[0];
$result = mysql_query($updatequery) or die (Query Failed:
$updatequery);
$numofrows = mysql_num_rows($result);
for ($i = 0; $i $numofrows; $i++)
{
$row = mysql_fetch_array($result);
echo td$row[0]/td;
}
}
I just want to use the result from the first query as input to my update
query. Any ideas what I am doing wrong?
Sincerely,
Chris Dietzler
ATT Enhanced Network Services
858 812 4062
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update problem
echo $updatequery to screen and see what the sql looks like.
I am trying to update a table from results generated from a select query,
but can't seem to get the queries running right. The select query works
fine, but when I try to use the results for an update it error out. Here
is
my query:
db_connect();
$query = SELECT cst_SiteID FROM customers WHERE cst_Offer = 1;
echo $query;
echo br/;
$result = mysql_query($query);
$numofrows = mysql_num_rows($result);
$numofcols = mysql_num_fields($result);
echo table\n;
for ( $r = 0; $r $numofrows; $r++)
{
$row = mysql_fetch_array($result);
echo td$row[0]/td;
$updatequery = UPDATE assets .
SET asset_Managed = '2' .
WHERE asset_SiteID = $row[0];
$result = mysql_query($updatequery) or die (Query Failed:
$updatequery);
$numofrows = mysql_num_rows($result);
for ($i = 0; $i $numofrows; $i++)
{
$row = mysql_fetch_array($result);
echo td$row[0]/td;
}
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update problem
You have to use UPDATE MyTABLE set MyTABLE.1 = myValue. Otherwise you are saying that the numeric value 1 = some other numeric value. God Bless GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update problem
Paul Thanks alot that help out alot. -Blake Paul McNeil wrote: You have to use UPDATE MyTABLE set MyTABLE.1 = myValue. Otherwise you are saying that the numeric value 1 = some other numeric value. God Bless GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: out of memory error and update problem
Chip Wiegand wrote: When running mysql from the command line (Putty terminal) and trying to do some updates the updates fail. First the query I am running - mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID between 75 and 100 AND warranty_temp.WarrantyID between 75 and 100; Query OK, 119 rows affected (50.97 sec) Rows matched: 14641 Changed: 119 Warnings: 0 This is not a join, it is a cartesian product of the 2 table ranges. The results of this query are that the OwnerName in record 75 is copied into the OwnerName for records 75 thru 100. That shouldn't be, there should be a differant OwnerName for each row being copied into each row of the target table. Now the table has 119 rows with the same OwnerName. mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID=warranty_temp.WarrantyID and warranty_old.WarrantyID between 75 and 100 ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
out of memory error and update problem
When running mysql from the command line (Putty terminal) and trying to do some updates the updates fail. First the query I am running - mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID between 75 and 100 AND warranty_temp.WarrantyID between 75 and 100; Query OK, 119 rows affected (50.97 sec) Rows matched: 14641 Changed: 119 Warnings: 0 The results of this query are that the OwnerName in record 75 is copied into the OwnerName for records 75 thru 100. That shouldn't be, there should be a differant OwnerName for each row being copied into each row of the target table. Now the table has 119 rows with the same OwnerName. I have also tried - mysql update warranty_old, warranty_temp set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID = warranty_temp.WarrantyID; This copied the OwnerName in row 1 into the OwnerName of every row in the table. Seems the only way to get this to work is to update every row individually - mysql update warranty, warranty_old set warranty.OwnerName=warranty_old.OwnerName where warranty.WarrantyID='39' AND warranty_old.WarrantyID='39'; which works, but there are thousands of rows to update. Now the memory error - when trying to run a large update, it fails and the log file contains this message - 040324 14:36:22 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space I don't know what 'ulimit' is. The server has 512 megs of swap space, I opened a second Putty terminal and watched top while running the update statements in another window and MySQL uses 95% of the memory while processing but the swap never gets touched (shows only 40K used). The server has 768megs ram, 600mhz Athlon processor. I just checked the dmesg messages and found this - pid 18729 (mysqld), uid 88 inumber 32818 on /: filesystem full so I ran this - su-2.05b# df -H FilesystemSize Used Avail Capacity Mounted on /dev/ad0s1a 508M 346M 121M74%/ devfs 1.0K 1.0K 0B 100%/dev /dev/ad0s1d14G 2.9G 9.9G22%/usr File system is not full, what's it talking about? What would be the recommended remedies? -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] There is no reason anyone would want a computer in their home. --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (Then why do I have 8? Somebody help me!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New to MySQL, ported from MS Access. Using ASP MyODBC - UPDATE problem...
Hi, I've recently made the move over from Access to MySQL. So far, everything has been fine. I'm using: Win2K server (ASP) WinMySQLadmin 1.4, MyCC 0.8.4-alpha, MyODBC 3.51 and MySQL 3.23.51-nt First problem: (fixed) -- I had used some keywords as table names, but SQL in a Nutshell by O'Reilly put me on the straight track. Second problem: (also fixed) Then I had problems using this line: % If MyRS.recordcount 0 Then %... I discovered that under ASP, the recordcount is -1, so I worked around this by using the following line: % If MyRS.EOF = False Then %. All was fine, and exporting data straight from Access2000 into MySQL was easy. I then found one bizarre problem: Third Problem: (Circumvented) - I could edit any old record set - originally created on Access, but not newly created ones! Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Query-based update failed because the row to update could not be found. I spent quite some time looking for information on how to fix this, and drew a blank. I can display all the info from the new record set, but I can't update it... ..that is until I tried a different way to update it: Old way: 1) Open connection (sql=select * from mytablename where UserID = '1') 2) MyRS(UserName) = New Value 3) MyRS.Update '-- Error occurred at this line 4) MyRS.Close New way: 1) Open connection with update directly in sql statement: (sql=UPDATE mytablename SET UserName = 'New Value' WHERE UserID = '1') So... here's my questions to you knowledgeable lot: a) Why did the second method work, and not the first? - is this a MyODBC bug? b) Why is this only happening to NEW rows in the database? - is it a MyODBC bug? c) could I gain/fix anything by changing over to MySQL 4 ? d) how do YOU normally update MySQL records from within ASP? Many thanks for any insight. As I said, I've made it work, so I'm not too troubled, but I would like to code things in the 'standard' way. Luis. - 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
Update problem in MySQL
Do anyone has a idea how to Insert (create SQLQuery) from onetable to an other table as decripted under: Here is the orginal Table: Table1: Ref | Timestamp | ActionDate ---+-+ 001 | DEP + 2002/01/02 ---+-+ 001 | ARR + 2002/01/04 ---+-+ 001 | DLV + 2002/01/15 ---+-+ 002 | ARR + 2002/02/02 ---+-+ 002 | DEP+ 2002/02/03 ---+-+ 002 | DLV + 2002/02/18 ---+-+ 003 | DEP + 2002/01/12 ---+-+ 003 | ARR + 2002/01/14 ---+-+ 003 | DLV + 2002/01/25 I should update Table one to Table2, which means that insted of 9 rows I should have row per Ref Table2: Ref | Departure | Arrival| Delivery ---+-+--+ 001 | 2002/01/02 + 2002/01/04 | 2002/01/15 ---+-+--+ 002 | 2002/02/02 + 2002/02/03 | 2002/02/18 ---+-+--+ 003 | 2002/01/12 + 2002/01/14 | 2002/01/25 ---+-+--+ Thanks for your help Juha - 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 problem in MySQL
Juha, select dep.Ref, dep.ActionDate as Departure, arr.ActionDate as Arrival, dlv.ActionDate as Delivery from Table1 dep left join Table1 arr on (dep.Ref = arr.Ref and arr.Timestamp = 'ARR') left join Table1 dlv on (dep.Ref = dlv.Ref and dlv.Timestamp = 'DLV') where dep.Ref = arr.Ref and dep.Timestamp = 'DEP' Best regards, Mikhail. - Original Message - From: Morsky Juha [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 10:06 AM Subject: Update problem in MySQL Do anyone has a idea how to Insert (create SQLQuery) from onetable to an other table as decripted under: Here is the orginal Table: Table1: Ref | Timestamp | ActionDate ---+-+ 001 | DEP + 2002/01/02 ---+-+ 001 | ARR + 2002/01/04 ---+-+ 001 | DLV + 2002/01/15 ---+-+ 002 | ARR + 2002/02/02 ---+-+ 002 | DEP+ 2002/02/03 ---+-+ 002 | DLV + 2002/02/18 ---+-+ 003 | DEP + 2002/01/12 ---+-+ 003 | ARR + 2002/01/14 ---+-+ 003 | DLV + 2002/01/25 I should update Table one to Table2, which means that insted of 9 rows I should have row per Ref Table2: Ref | Departure | Arrival| Delivery ---+-+--+ 001 | 2002/01/02 + 2002/01/04 | 2002/01/15 ---+-+--+ 002 | 2002/02/02 + 2002/02/03 | 2002/02/18 ---+-+--+ 003 | 2002/01/12 + 2002/01/14 | 2002/01/25 ---+-+--+ Thanks for your help Juha - 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
SQL - Update problem
Hi, how can I do sql command like this: update sekce set pocet = count(*) from odkaz where odkaz.sekce_id = sekce.id Thanks for answer j. - 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: SQL - Update problem
you cant update from is NOT supported -Original Message- From: Jakub Mach [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:15 AM To: Mysql Subject: SQL - Update problem Hi, how can I do sql command like this: update sekce set pocet = count(*) from odkaz where odkaz.sekce_id = sekce.id Thanks for answer j. - 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
[select into] RE: SQL - Update problem
Hmm .. isn't there any workaround for this .. Maybe something like: select count(*) into :somerVar FROM table; update sekce set pocet = :someVar WHERE ... blabla; I've seen things like this in other databas servers but haven't been able to find out if it's supported by MySQL, and if so, how... Thanks, Wouter -Oorspronkelijk bericht- Van: Mary Stickney [mailto:[EMAIL PROTECTED]] Verzonden: August 28, 2002 16:37 Aan: Jakub Mach; Mysql Onderwerp: RE: SQL - Update problem you cant update from is NOT supported -Original Message- From: Jakub Mach [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 9:15 AM To: Mysql Subject: SQL - Update problem Hi, how can I do sql command like this: update sekce set pocet = count(*) from odkaz where odkaz.sekce_id = sekce.id Thanks for answer j. - 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 - 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: SQL - Update problem
Jakub, Wednesday, August 28, 2002, 5:15:01 PM, you wrote: JM how can I do sql command like this: JM update sekce set pocet = count(*) from odkaz where odkaz.sekce_id = sekce.id First: multi-table updates is supported only since 4.0.2 Second: even on 4.0.2 you can't do it with above query -- 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: SQL - Update problem
Hi, ...adding a new query and using variable: SET @a:=0; --initialize variable a; select @:=COUNT(*) from odkaz where odkaz.sekce_id = sekce.id; update sekce set pocet = @a where odkaz.sekce_id = sekce.id; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jakub Mach [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 5:15 PM Subject: SQL - Update problem Hi, how can I do sql command like this: update sekce set pocet = count(*) from odkaz where odkaz.sekce_id = sekce.id Thanks for answer j. - 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: SQL - Update problem
Hi, Egor has right and i didn't read very carefully you e-mail.Sorry You can try this : SET @a:=0; SET @b:=0; SELECT @a:=COUNT(*),@b:=sekce.id FROM odkaz,sekce WHERE odkaz.sekce_id = sekce.id; UPDATE sekce SET pocet = @a WHERE id= @b; Now...i think it's corect... Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jakub Mach [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 5:15 PM Subject: SQL - Update problem Hi, how can I do sql command like this: update sekce set pocet = count(*) from odkaz where odkaz.sekce_id = sekce.id Thanks for answer j. - 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
Update problem
Good afternoon! I have a table of subscribers from which I select data to create another table which will indicate which of these subscribers should get a reminder letter. I need to set a column in the subscriber file indicating the date of the reminder, so that the subscriber will not be sent another reminder. So I need to compare subscriber to reminder and set a column in subscriber based on matches in reminder; UPDATE Subscriber SET Subscriber.DateOfReminderLetter = CURRENT_DATE() WHERE Subscriber.IDN = Reminder.IDN Of course you cannot do that. I have searched the documentation. There is also this; INSERT INTO Subscriber (ReminderDate) SELECT Reminder.ReminderDate FROM Reminder, Subscriber WHERE Reminder.IDN = Subscriber.IDN Which you also cannot do. Anyone have any ideas on how this can be done? TIA!! Jay sql, mysql, query The very existence of flame throwers indicates that someone once thought, Hey, I'd like to set that guy over there on fire. -- George Carlin - 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
on update problem?
When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - 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: on update problem?
Hi! don't think ON UPDATE is supported. The manual : http://www.innodb.com/ibman.html doesn't mention anything about it except : Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. EG When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - 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: on update problem?
It would seem strange to implement one and not the other? The help does say that you can use it and does keep the create options now. Does anyone know if this will be implemented in the next .5x release? Ross Me writes: Hi! don't think ON UPDATE is supported. The manual : http://www.innodb.com/ibman.html doesn't mention anything about it except : Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. EG When I try to change the id field in the parent table below, I get the error: 1217 - Cannot delete a parent row; a foreign key constraint fails. What have I got wrong? Shouldn't it cascade the changes that were made to the parent down to the child table? This is done with Mysql 3.23.50-max on Windows NT Here is the create that I am using for the test CREATE TABLE parent(id INT unsigned NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; create table child ( id int unsigned not null auto_increment , parent_id int unsigned not null , name varchar(20) null , primary key (id) , index parent_id(parent_id) FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) TYPE=INNODB; Thanks in advance - 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: new bee - Mysql Database Update Problem
It seems like you have hit a file size limit. Soalris has a 2 Gig ( or is it 4Gig) file size limit which is restricting your database. You could get past this by breaking up your data into different segments, eg. group the data by month and store it in monthly tables, reducing the size of the datafile, and then use a merge table to group it all back together into a virtual table. cheers richard samit darne [EMAIL PROTECTED] 01/11/02 09:25PM Hello, I am new to MYSQL platform: Sun Solaris 2.6 mysql version: 3.22.32 I upgrade the hard drive from 4G to 18G as database was increasing and causing problem now space problem is solved and got in to another problem. Database is not getting updated through C programs and perl scripts( that takes the raw data and update in database) which were working fine before the Hard drive upgrade. While C program is running looking at processlist I actually see data being inserted for a particular day in a particular table. After the program finishes I do a query for that particular day on a particular table I don't see any result. I get Empty Set. When I manually insert an entry and do query I do get result for that entry. I don't know what's going on has anyone ran in to this problem? Thanks __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Content-Type: text/plain; Content-Disposition: attachment; filename=disclaimer.txt . 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
new bee - Mysql Database Update Problem
Hello, I am new to MYSQL platform: Sun Solaris 2.6 mysql version: 3.22.32 I upgrade the hard drive from 4G to 18G as database was increasing and causing problem now space problem is solved and got in to another problem. Database is not getting updated through C programs and perl scripts( that takes the raw data and update in database) which were working fine before the Hard drive upgrade. While C program is running looking at processlist I actually see data being inserted for a particular day in a particular table. After the program finishes I do a query for that particular day on a particular table I don't see any result. I get Empty Set. When I manually insert an entry and do query I do get result for that entry. I don't know what's going on has anyone ran in to this problem? Thanks __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ - 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
UPDATE problem
Hi, I am encountering a problem while I am updating records of a table. Basically its a problem of updation of records using pattern matching. I want all the recods that end with letter s be updated to record without the letter s at the end. For eg, I want a record things to be converted to thing. When I give a query like this I get an error : mysql UPDATE words_bak1 SET word = % WHERE word LIKE %s; ERROR 1062: Duplicate entry '%' for key 1 my words_bak1 is like this : ++--+--+-+-++ | Field | Type | Null| Key | Default | Extra | ++--+--+-+-++ | wordid | int(11) | | PRI | 0 | auto_increment | | word | char(20) | | UNI | | || | actualword | char(20) | YES | | NULL|| ++--+--+-+-++ Can someone tell me what the problem is and is there some way to get around it.. -Amit - 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 problem
imho, the fact that mysql does not support joins in UPDATE and DELETE statements is by far its greatest weakness. I have posted a couple of messages to this group to see what creative work-arounds others have used to fill this gaping hole in functionality. Alas, no one has felt like sharing their thoughts on this subject. Will (and Phil) I think no-one's replied because the answer is really simple - create a recordset using Select for the records you are interested in (using your joins), then use that recordset to perform your UPDATE or DELETE. I have to deal with the same situation Phil describes quite often, but I've never missed the 'join in update' function. Not that it wouldn't be nice, but the workaround is not rocket surgery. Jay Fesco - 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 problem
I think no-one's replied because the answer is really simple Oh I don't know Jay, I've seen some threads 4-5 responses deep to issues far simpler than this. create a recordset using Select for the records you are interested in (using your joins), then use that recordset to perform your UPDATE or DELETE. I understand that you could use REPLACE with this newly created recordset to simulate the UPDATE w/JOIN, but your it's so simple response glosses over the details of what to do with the delete. The only way I've conceived is to carry an isdeleted field which is set using the replace command, then used in where clause of a DELETE. Of course I may be missing a simpler or better way to do this... which is why I made my original post. I have to deal with the same situation Phil describes quite often, but I've never missed the 'join in update' function. Well I'm happy for you but did you ever stop and consider that what you do with your database may not necessarily be representative of what everyone does? In my situation, one that is certainly not unique, I have tables with 100+ fields and many million rows. The SELECT INTO/REPLACE FROM and SELECT INTO/REPLACE FROM/DELETE WHERE approaches to UPDATE FROM and DELETE FROM are an order of magnitude less efficient. This inefficiency coupled with the need (using myisam) to lock the entire table during updates makes for some not-so-simple design challenges. In closing, I offer a couple rhetorical questions: 1. Who is the bigger idiot... The person who asks a naive question or the person who, upon hearing a question he knows the answer to, assumes the asker is an idiot? 2. Does your condescending attitude win you as many points with your users as it did with me? Will French -Original Message- From: Jay Fesco [mailto:[EMAIL PROTECTED]] Sent: Monday, September 24, 2001 8:30 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Update problem imho, the fact that mysql does not support joins in UPDATE and DELETE statements is by far its greatest weakness. I have posted a couple of messages to this group to see what creative work-arounds others have used to fill this gaping hole in functionality. Alas, no one has felt like sharing their thoughts on this subject. Will (and Phil) I think no-one's replied because the answer is really simple - create a recordset using Select for the records you are interested in (using your joins), then use that recordset to perform your UPDATE or DELETE. I have to deal with the same situation Phil describes quite often, but I've never missed the 'join in update' function. Not that it wouldn't be nice, but the workaround is not rocket surgery. Jay Fesco - 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 problem
Will, First of all, if I sounded condescending, it was not my intent. I apologize if that's how you (or anyone else) took it. I will, however, respond to your smoking response to mine: I think no-one's replied because the answer is really simple Oh I don't know Jay, I've seen some threads 4-5 responses deep to issues far simpler than this. That does not make those questions and answers on-topic and appropriate or your question any more complex. I understand that you could use REPLACE with this newly created recordset to simulate the UPDATE w/JOIN, but your it's so simple response glosses over the details of what to do with the delete. The only way I've conceived is to carry an isdeleted... Perhaps you're using the wrong scripting language. Just because you have a hammer does not make everything a nail. Well I'm happy for you but did you ever stop and consider that what you do with your database may not necessarily be representative of what everyone does? Let's see - I read records, I do something with them, I update and delete them... No, I never stopped to think that you (or anyone) might be doing something different. In my situation, one that is certainly not unique, I have tables with 100+ fields and many million rows. The SELECT INTO/REPLACE FROM and SELECT INTO/REPLACE FROM/DELETE WHERE approaches to UPDATE FROM and DELETE FROM are an order of magnitude less efficient. This inefficiency coupled with the need (using myisam) to lock the entire table during updates makes for some not-so-simple design challenges. So you feel that forcing a lock while you do a huge join/delete is more efficient than: * Doing a select (using joins) against your properly indexed table (which should NOT require a lock in that you only want the record ID's) * Using the method of your choosing, deleting or updating the records so identified? I don't know the benchmarking which compares a WHERE IN() versus issuing individual updates/deletes while the table is open, but it would be easy enough to test. In closing, I offer a couple rhetorical questions: 1. Who is the bigger idiot... The person who asks a naive question or the person who, upon hearing a question he knows the answer to, assumes the asker is an idiot? Never did I assume that you were an idiot. What I assumed (and still do) is that you are overcomplicating the question. 2. Does your condescending attitude win you as many points with your users as it did with me? Will French I haven't had a complaint from my users yet. Again, If I sounded condescending, it was not my intent. Jay Fesco - 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
Update problem
I am having problems getting an update to work correctly. Assume that I have two tables, employee and office. Employee has two fields, employee_id and employee_name, and office has three fields, office_id, office_number, employee_id. I have been using inner join to link the tables together when pulling information out of the database, but I can't get join to work with update. Say I want to update the office_number for a particular employee. I would have to get the employee_id from the employee table by referencing the employee_name in order to update the row with the corresponding employee_id in the office table. Thus far I have been trying statements like this: update office inner join employee using (employee_id) set office_number=XXX where employee_name=XX; I know that I could work around this via Perl, but I would rather have mysql do the work. Any help would be appreciated, Thanks, Phil Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ - 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 problem
In answer to your question - your statement does not work becuase mysql does not allow joins in update statements. imho, the fact that mysql does not support joins in UPDATE and DELETE statements is by far its greatest weakness. I have posted a couple of messages to this group to see what creative work-arounds others have used to fill this gaping hole in functionality. Alas, no one has felt like sharing their thoughts on this subject. -Original Message- From: Philip Montgomery [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 23, 2001 10:35 PM To: [EMAIL PROTECTED] Subject: Update problem I am having problems getting an update to work correctly. Assume that I have two tables, employee and office. Employee has two fields, employee_id and employee_name, and office has three fields, office_id, office_number, employee_id. I have been using inner join to link the tables together when pulling information out of the database, but I can't get join to work with update. Say I want to update the office_number for a particular employee. I would have to get the employee_id from the employee table by referencing the employee_name in order to update the row with the corresponding employee_id in the office table. Thus far I have been trying statements like this: update office inner join employee using (employee_id) set office_number=XXX where employee_name=XX; I know that I could work around this via Perl, but I would rather have mysql do the work. Any help would be appreciated, Thanks, Phil Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ - 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
UPDATE problem
Hello, I have created a predictionscompetition and some code counts te total score of a match of a person. On the screen it workes, but in de database it goes wrong. Each person gets the total of the last person. There must be something wrong with the update statement. I hope someone can help me. Cheers, Martin. $number = MYSQL_NUMROWS($result); $i = 0; WHILE ($i $number): $naam = mysql_result($result,$i,naam); $uit_gr = mysql_result($result,$i,uit_gr); $uit_te = mysql_result($result,$i,uit_te); $ru_gr = mysql_result($result,$i,ru_gr); $ru_te = mysql_result($result,$i,ru_te); $minuut = mysql_result($result,$i,minuut); $toto = mysql_result($result,$i,toto); /* Count everything*/ $t01 = $u1+$r1+$b1+$m1+$t1; $sqlb = UPDATE voorspelling1 SET totaal='$t01' WHERE id_voorspelling = '$id_voorspelling'; $resultb = mysql_query($sqlb); PRINT $naam $uit_gr $uit_te $u1 $r1 $b1 $m1 $t1 $t01BR; PRINT $t1BR; $i++; ENDWHILE;
Update problem, or more likely and problem understanding how to do updates.
Ok, based on the given syntax from the mysql manual.. UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #] I've been trying to update some records in a test database I've been playing with. I'm inexperienced with MySQL, so I'm hoping someone out there can enlighten me. When trying to update more than one field at a time, I get the following error: ERROR 1064: You have an error in your SQL syntax near 'set Notes = 'Test' where ID ='1'' at line 1 I was trying to perform the following query to change the data below. mysql select * from Pictures; +++---+--+---+-+ +---++ | ID | Category | Title | Filename | Submitter | Date_Posted | Poster | Notes | Updated| +++---+--+---+-+ +---++ | 1 | Animal Funnies | pic1 | 0001.jpg | Kiwi | 2001-05-08 | WRC| aaa | 0105111248 | | 2 | Animal Funnies | asdfa | 0002.jpg | dsoTm | 2001-05-10 | BLJ| | 0105111036 | | 3 | Animal Funnies | test | 0003.jpg | | 2001-05-10 | BLJ| | 0105102219 | +++---+--+---+-+ +---++ update Pictures set Date_Posted = '2001-05-09', set Notes = 'Test' where ID ='1'; It looks syntatically correct according to the example in the manual, but no matter which combination of fields I try and update, I get that error. Single field updates for all fields works fine. The table is defined as follows: ID = mediumint(8) unsigned NOT NULL auto_increment Category and Poster = enumerated. Title, Filename, Submitter = varchar Date_Posted = date Notes = tinytext Updated = timestamp Please reply directly as I am not on the list. Thanks for your time. - 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 problem, or more likely and problem understanding how to do updates.
I don't believe you need the second set update Pictures set Date Posted = '2001-05-09', Notes = 'Test' where ID ='1'; I think that should work -Original Message- From: Barry L. Jeung [mailto:[EMAIL PROTECTED]] Sent: Friday, May 11, 2001 3:56 PM To: [EMAIL PROTECTED] Subject: Update problem, or more likely and problem understanding how to do updates. Ok, based on the given syntax from the mysql manual.. UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #] I've been trying to update some records in a test database I've been playing with. I'm inexperienced with MySQL, so I'm hoping someone out there can enlighten me. When trying to update more than one field at a time, I get the following error: ERROR 1064: You have an error in your SQL syntax near 'set Notes = 'Test' where ID ='1'' at line 1 I was trying to perform the following query to change the data below. mysql select * from Pictures; +++---+--+---+-+ +---++ | ID | Category | Title | Filename | Submitter | Date_Posted | Poster | Notes | Updated| +++---+--+---+-+ +---++ | 1 | Animal Funnies | pic1 | 0001.jpg | Kiwi | 2001-05-08 | WRC| aaa | 0105111248 | | 2 | Animal Funnies | asdfa | 0002.jpg | dsoTm | 2001-05-10 | BLJ| | 0105111036 | | 3 | Animal Funnies | test | 0003.jpg | | 2001-05-10 | BLJ| | 0105102219 | +++---+--+---+-+ +---++ update Pictures set Date_Posted = '2001-05-09', set Notes = 'Test' where ID ='1'; It looks syntatically correct according to the example in the manual, but no matter which combination of fields I try and update, I get that error. Single field updates for all fields works fine. The table is defined as follows: ID = mediumint(8) unsigned NOT NULL auto_increment Category and Poster = enumerated. Title, Filename, Submitter = varchar Date_Posted = date Notes = tinytext Updated = timestamp Please reply directly as I am not on the list. Thanks for your time. - 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 problem, or more likely and problem understanding how to do updates.
You only need to specify SET once. update Pictures set Date_Posted = '2001-05-09', Notes = 'Test' where ID ='1'; - Original Message - From: Barry L. Jeung [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 11, 2001 12:55 PM Subject: Update problem, or more likely and problem understanding how to do updates. Ok, based on the given syntax from the mysql manual.. UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #] I've been trying to update some records in a test database I've been playing with. I'm inexperienced with MySQL, so I'm hoping someone out there can enlighten me. When trying to update more than one field at a time, I get the following error: ERROR 1064: You have an error in your SQL syntax near 'set Notes = 'Test' where ID ='1'' at line 1 I was trying to perform the following query to change the data below. mysql select * from Pictures; +++---+--+---+-+ +---++ | ID | Category | Title | Filename | Submitter | Date_Posted | Poster | Notes | Updated| +++---+--+---+-+ +---++ | 1 | Animal Funnies | pic1 | 0001.jpg | Kiwi | 2001-05-08 | WRC| aaa | 0105111248 | | 2 | Animal Funnies | asdfa | 0002.jpg | dsoTm | 2001-05-10 | BLJ| | 0105111036 | | 3 | Animal Funnies | test | 0003.jpg | | 2001-05-10 | BLJ| | 0105102219 | +++---+--+---+-+ +---++ update Pictures set Date_Posted = '2001-05-09', set Notes = 'Test' where ID ='1'; It looks syntatically correct according to the example in the manual, but no matter which combination of fields I try and update, I get that error. Single field updates for all fields works fine. The table is defined as follows: ID = mediumint(8) unsigned NOT NULL auto_increment Category and Poster = enumerated. Title, Filename, Submitter = varchar Date_Posted = date Notes = tinytext Updated = timestamp Please reply directly as I am not on the list. Thanks for your time. - 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
Update problem.
Hello! Need some help. I have two tables t1 with fields id and site t2 with fields id, from, to, koef I want to update t2 set koef=2 if the from field in t2 table exist in t1.url such update return error. update t2, t1 set t2.koef=0 where t2.from=t1.site Thank yoo very much. - 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 problem.
petro writes:
Hello!
Need some help.
I have two tables
t1 with fields id and site
t2 with fields id, from, to, koef
I want to update t2 set koef=2 if the from field in t2 table exist in
t1.url
such update return error.
update t2, t1 set t2.koef=0 where t2.from=t1.site
Thank yoo very much.
Hi!
This kind of update is in the works on the 4.0 branch.
With 3.23, if you can program in C++, you may take a look at
updel_x.cc example in MySQL++ source distro.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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
