November 30, 2005 at 4:50 am
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
November 30, 2005 at 4:59 am
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
November 30, 2005 at 5:41 am
Kenneth
Thx I receive next error :
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.
JV
November 30, 2005 at 5:51 am
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
November 30, 2005 at 5:53 am
(0 row(s) affected) is the result. This should be 167 rows.
JV
November 30, 2005 at 6:05 am
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
December 1, 2005 at 5:08 am
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..
December 1, 2005 at 8:44 am
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'
December 1, 2005 at 3:51 pm
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
December 2, 2005 at 6:38 am
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
December 5, 2005 at 1:59 am
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