Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Unanswered: How to update multiple mysql rows...

    Hi,

    Before you start reading, if something is not clear, please comment.

    Let's start:

    I would like to update multiple mysql rows from php.

    the variables to determinate the updates will be obtained from a query:

    mysql_query("select * from productos where SESSION_ID=."'$sesion."'');

    the result, obviously will have more than 1 row. One of those values, QTY, needs to be updated in another table. This will be the query results:
    Table name: SALES


    | ID | CODIGO | QTY | SESSION_ID |

    1 2SB1522 10 665sdad654asd6545sdsds
    2 2SA1212 5 665sdad654asd6545sdsds
    3 2SC1522 2 665sdad654asd6545sdsds


    My other table is like this (The one I would like to update):

    Table name: PRODUCTS

    | ID | CODIGO | QTY |
    1 2SB1522 5
    2 2SA1212 2
    3 2SC1522 1


    Actually, the only field that I need to update is QTY.

    also, I need this operation on the fly = productos.QTY minus sales.QTY

    The result from this operation will be the value to be inserted in the update.

    Again, all the rows must be updated, following the same operation.


    Please let me know the script I need to use. THANKS !!!!!!!
    Last edited by Mysticav; 07-10-04 at 13:32.

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    Assuming "codigo" is the unique key:

    UPDATE products, sales SET products.qty=products.qty-sales.qty
    WHERE products.codigo=sales.codigo;

    See:
    http://dev.mysql.com/doc/mysql/en/UPDATE.html

  3. #3
    Join Date
    Dec 2003
    Location
    Houston, TX
    Posts
    21
    Quote Originally Posted by yellowmarker
    Assuming "codigo" is the unique key:

    UPDATE products, sales SET products.qty=products.qty-sales.qty
    WHERE products.codigo=sales.codigo;

    See:
    http://dev.mysql.com/doc/mysql/en/UPDATE.html

    Even better ...

    UPDATE products, sales SET products.qty=products.qty-sales.qty, <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>', <fieldname> = '<value>'
    WHERE products.codigo=sales.codigo;
    I do not fear computers. I fear the lack of them.
    -Isaac Asimov (1920 - 1992)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •