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