FAQ
Hello i can't figure out how to update the table tmpISM based on other
table ta values. What i need i can intuitivly express as follows:

update tmpISM
set tmp.SUMA=tmp.SUMA-ta.SUMA
where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)

index PID+PAJ is unique both for tmpISM and ta
Any suggestions how the update should look like (i suspect it will
have to have sub-select)

Search Discussions

  • Philippe Poelvoorde at Aug 5, 2004 at 11:40 am

    DebugasRu wrote:

    Hello i can't figure out how to update the table tmpISM based on other
    table ta values. What i need i can intuitivly express as follows:

    update tmpISM
    set tmp.SUMA=tmp.SUMA-ta.SUMA
    where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)
    try:

    update tmpISM,ta
    set tmp.SUMA=tmp.SUMA-ta.SUMA
    where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)



    --
    Philippe Poelvoorde
    COS Trading Ltd.
  • DebugasRu at Aug 5, 2004 at 12:10 pm
    PP> try:
    PP> update tmpISM,ta
    PP> set tmp.SUMA=tmp.SUMA-ta.SUMA
    PP> where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)

    Does update work on more than one table ?

    thank you for your input but i've just resolved the problem as follows
    (i have had forgotten the EXISTS keyword for a moment):

    update tmp
    set tmp.SUMA=tmp.SUMA-ta.SUMA
    where exists (
    select * from tmp,ta
    where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)
    )
  • DebugasRu at Aug 5, 2004 at 12:22 pm
    D> update tmp
    D> set tmp.SUMA=tmp.SUMA-ta.SUMA
    D> where exists (
    D> select * from tmp,ta
    D> where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)
    D> )

    bah, it does not understand -ta.SUMA on the second line
    so i have to stick with Philippe's suggestion ( thanks for the help ):

    PP>> update tmpISM,ta
    PP>> set tmp.SUMA=tmp.SUMA-ta.SUMA
    PP>> where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)
  • Philippe Poelvoorde at Aug 5, 2004 at 2:54 pm

    DebugasRu wrote:

    PP> try:
    PP> update tmpISM,ta
    PP> set tmp.SUMA=tmp.SUMA-ta.SUMA
    PP> where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)

    Does update work on more than one table ?
    Yes you can :
    http://dev.mysql.com/doc/mysql/en/UPDATE.html
    thank you for your input but i've just resolved the problem as follows
    (i have had forgotten the EXISTS keyword for a moment):

    update tmp
    set tmp.SUMA=tmp.SUMA-ta.SUMA
    where exists (
    select * from tmp,ta
    where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)
    )
    Cool, I didn't it exists :)

    --
    Philippe Poelvoorde
    COS Trading Ltd.
  • Sgreen at Aug 5, 2004 at 1:09 pm
    Have you tried:

    UPDATE tmpISM
    INNER JOIN ta
    ON tmp.PID = ta.PID
    AND tmp.PAJ = ta.PAJ
    SET tmp.SUMA = tmp.SUMA-ta.SUMA

    Shawn Green
    Database Administrator
    Unimin Corporation - Spruce Pine

    DebugasRu <debugas@list.ru> wrote on 08/05/2004 07:22:57 AM:
    Hello i can't figure out how to update the table tmpISM based on other
    table ta values. What i need i can intuitivly express as follows:

    update tmpISM
    set tmp.SUMA=tmp.SUMA-ta.SUMA
    where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ)

    index PID+PAJ is unique both for tmpISM and ta
    Any suggestions how the update should look like (i suspect it will
    have to have sub-select)



    --
    MySQL General Mailing List
    For list archives: http://lists.mysql.com/mysql
    To unsubscribe: http://lists.mysql.com/mysql?unsub=sgreen@unimin.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupmysql @
categoriesmysql
postedAug 5, '04 at 11:23a
activeAug 5, '04 at 2:54p
posts6
users3
websitemysql.com
irc#mysql

People

Translate

site design / logo © 2021 Grokbase