Update query in table from same table

  • Hi

    Can this work ?

    update ventven vv1

    set vv1.mtht = vv2.mtht,

    vv1.mthtsp = vv2. mthtsp,

    vv1.mthtax = vv2.mthtax,

    vv1.mttva, = vv2.mttva,

    vv1.mtttc = vv2.mtttc,

    vv1.netrem = vv2.netrem,

    vv1.mthtre = vv2.mthtre,

    vv1.netesc = vv2.netesc,

    vv1.mosoco = vv2.mosoco

    from (select * from ventven where refcfa = 'VERD-000015') as vv2

    join ventven on vv1.nofact = vv2.nofact

    where vv1.refcfa = 'VERD-000016'

    and vv1.codnat = 'g'

     

    The select * from evntven where recfa = 'VERD-000015' are the creditnotes and i want to have the invoices (ventven) to have the exact amount as the creditnotes.

    Thx in advance.

    El Jefe


    JV

  • Well, have you tried it..?

    While it looks like it would work, it's imo unnecessary resourcedemanding to write the join with a derived table in this case.

    You could instead do a 'normal' join:

    update vv1

    set  ......

    from ventven as vv1

    join ventven as vv2

    on   vv1.nofact = vv2.nofact

    and  vv2.refcfa = 'VERD-000015'

    and  vv1.refcfa = 'VERD-000016'

    and  vv1.codnat = 'g'

    /Kenneth

  • Kenneth

     

    Thx I receive next error :

    Server: Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'as'.

     

     


    JV

  • I can parse this one :

    update ventven

    set ventven.mtht = vv2.mtht,

    ventven.mthtsp = vv2. mthtsp,

    ventven.mthtax = vv2.mthtax,

    ventven.mttva = vv2.mttva,

    ventven.mtttc = vv2.mtttc,

    ventven.netrem = vv2.netrem,

    ventven.mthtre = vv2.mthtre,

    ventven.netesc = vv2.netesc,

    ventven.mosoco = vv2.mosoco

    from ventven as vv2

    join ventven 

    on   ventven.nofact = vv2.nofact

    and  vv2.refcfa = 'VERD-000015'

    and  ventven.refcfa = 'VERD-000016'

    and  ventven.codnat = 'g'

     

    i cannot use update ventven as vv1

    Can I trust it to check with the right data ?

     

    Thx


    JV

  • (0 row(s) affected) is the result. This should be 167 rows.


    JV

  • ok, this one works 🙂

    use sn

    update ventven

    set ventven.mtht = vv2.mtht,

    ventven.mthtsp = vv2. mthtsp,

    ventven.mthtax = vv2.mthtax,

    ventven.mttva = vv2.mttva,

    ventven.mtttc = vv2.mtttc,

    ventven.netrem = vv2.netrem,

    ventven.mthtre = vv2.mthtre,

    ventven.netesc = vv2.netesc,

    ventven.mosoco = vv2.mosoco

    from ventven as vv2

    join ventven 

    on   ventven.nomat = vv2.nomat

    and  vv2.refcfa = 'VERD-000015'

    and  ventven.refcfa = 'VERD-000016'

    and  ventven.codnat = 'g'


    JV

  • You are correct - the problem was that you cannot have an alias on both ventven tables in the from clause - one of them must be unaliased.  OR, you can do what were doing originally which was to have an alias on the ventven table and then to avoid confusion about the resolution of tables you use a derived table...  Your last solution is the most elegant, but if your table names are very long, then perhaps the first one is better (with the derived table).

    I suppose SQL may also have trouble generating efficient query plans with the derived table, although I haven't investigated if that is the case..

  • I always alias all tables in multitable queries.  It tends to make the query much clearer and cleaner.

    update vv1 set vv1.mtht = vv2.mtht,

    vv1.mthtsp = vv2. mthtsp,

    vv1.mthtax = vv2.mthtax,

    vv1.mttva = vv2.mttva,

    vv1.mtttc = vv2.mtttc,

    vv1.netrem = vv2.netrem,

    vv1.mthtre = vv2.mthtre,

    vv1.netesc = vv2.netesc,

    vv1.mosoco = vv2.mosoco

    from ventven vv2

    inner join ventven vv1

    on   vv1.nomat = vv2.nomat

    and  vv2.refcfa = 'VERD-000015'

    and  vv1.refcfa = 'VERD-000016'

    and  vv1.codnat = 'g'

  • update vv1 set

    For all the times I've done things like this I had never thought of using the table alias in the update statement... Thanks for the syntax

  • Hi guys

    That query was for the head of the orders. Now I have to change the orderlines 😉

    This is the query :

    update vl1

    set vl1.mttht = vl2.mttht*-1,

    vl1.mtnhtl = vl2.mtnhtl*-1

    from vligven vl2

    inner join vligven vl1

    on vl1.nomat = vl2.nomat

    and vl1.codart = vl2.codart

    where vl1.nofact between '20107857' and '20107903'

    and vl2.nofact between '20107393' and '20107559'

    In the join i have two conditions :

    The customer (nomat) should be the same as should the articlecode (codart).

    I only get 138 lines in the query. It should be more : 167 * 3 = 501 records.

    How can this be ?

    Thx in advance

     


    JV

  • For the most parts, we 'get answers to what we ask'... So, check your data. Does it look like you expect? Easiest way to validate an update is to do a select with the same join/searchcriteria and have a look at what comes back. Revisit your query. Does it ask the question you intend it to? (I can't answer that without access to your tables and the actual data)

    /Kenneth

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply