update statements problem
I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks!
Re: update statements problem
Hi, I think your problem is that OR and AND do not have the same precedence, AND binds tighter. So what you need is probably: (products_model=5217-01 OR products_model=5217-01) AND products_um=CS and the same for PK and EA. The way you have you'll get an update as soon as products_model=5217-01 /Johan cybermalandro cybermalandro wrote: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 2006-03-17 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update statements problem
cybermalandro cybermalandro [EMAIL PROTECTED] wrote on 03/20/2006 11:00:51 AM: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! It has to do with the expression you are using to pick which row to update WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS This is parsed as WHERE products_model=5217-01 OR (products_model=521701 AND products_um=CS) But what I think you wanted to say was WHERE (products_model=5217-01 OR products_model=521701) AND products_um=CS Add the parentheses around your OR terms and you should only be changing what you wanted to change. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: update statements problem
Hey guys! thanks a bunch that really fixed my problem. Thanks for your prompt response. cybm On 3/20/06, Johan Höök [EMAIL PROTECTED] wrote: Hi, I think your problem is that OR and AND do not have the same precedence, AND binds tighter. So what you need is probably: (products_model=5217-01 OR products_model=5217-01) AND products_um=CS and the same for PK and EA. The way you have you'll get an update as soon as products_model=5217-01 /Johan cybermalandro cybermalandro wrote: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 2006-03-17
