April 2, 2009 at 4:59 pm
Hello comunity
i have this script where i see on my table PC, that the field descricao for the same field conta are not the same on year 2006 and year 2009.
I can see this runnig this query.
SELECT pc.conta, pc.descricao ,pc2009.conta,pc2009.descricao
FROM (SELECT conta,descricao FROM pc WHERE ano=2009) pc2009
INNER JOIN pc ON pc2009.conta=pc.conta
WHERE pc.ano = 2006 AND pc2009.descricao <> pc.descricao
AND pc.conta LIKE '6%'
I´am pretend make an Update to the field DESIGN where year = 2006 and conta as the same for both year (2206 and 2009)
i try this , but they don´t work :
UPDATE Pc
SET pc.descricao = (SELECT descricao FROM pc WHERE ano=2009) pc2009
INNER JOIN pc ON pc2009.conta = pc.conta
WHERE pc.ano = 2006 AND pc2009.descricao <> pc.descricao
AND pc.conta LIKE '6%'
error : Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'pc2009'.
someone have an idea ?
Thanks
Luis Santos
April 2, 2009 at 5:37 pm
Luis, you have no FROM clause. You can't do an inner join without one.
If the inner join is supposed to be to the table being updated just use that in the FROM clause like this. Be sure to give it a distinct name you can join on.
UPDATE pc
set col = something
FROM pc pcalias
INNER JOIN pc2009 on pc2009.conta = pcalias.conta
etc
etc
etc
That said, there is no need to alias that subquery in the select statement. You don't need to join to it. Are you trying to do something similar to the CROSS APPLY solution I referred you to in another post?
Try something like this
UPDATE Pc
SET pc.descricao = pc2009.descricao
FROM pc pc1
CROSS APPLY
(SELECT descricao FROM pc pc2 WHERE pc2.ano=2009 and pc2.conta = pc1.conta) pc2009
WHERE pc1.ano = 2006
AND pc1.conta LIKE '6%'
-- AND pc2009.descricao <> pc.descricao -- not needed and may slow you down
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 3, 2009 at 4:32 am
Hello Bob
Thanks for your script they work OK.
I need to explore better the "CROSS APPLY" and there use.
Many thanks
Luis Santos
April 3, 2009 at 7:30 am
You're welcome, Luis.
CROSS APPLY is a very useful technique to "join" both subqueries and Inline Table Valued Functions. The optimizer generates very efficient execution plans when I've used this technique. (Please note that there is a huge performance difference between multiline table functions and inline table valued functions).
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply