Update query in table from same table

Question

Update query in table from same table

jvElecenter

SSCarpal Tunnel

Points: 4502
More actions
November 30, 2005 at 4:50 am

#95961

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

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

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

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 2

Kenneth

Thx I receive next error :

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

Incorrect syntax near the keyword 'as'.

JV

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 3

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

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 4

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

JV

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 5

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

Ian Yates SSCoach Points: 19738 More actions · Answer 6

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..

scking SSCrazy Points: 2598 More actions · Answer 7

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'

Ian Yates SSCoach Points: 19738 More actions · Answer 8

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

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 9

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

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