Update the result of an inner join query

Question

Update the result of an inner join query

Teresita Castro

SSCommitted

Points: 1568
More actions
May 17, 2006 at 1:13 pm

#366203

I want to update the field noTarjeta on my table tbFactuLineas, but the condition to know if I can update it is in tbFactuCabezas, how can I do that.
I was thinking on this but it isn't works:

UPDATE (
select tbfactulineas.NOTARJETA from tbfactucabecera
inner join tbfactulineas on
tbfactucabecera.nocliente=tbfactulineas.nocliente
and
tbfactucabecera.noOrden=tbfactulineas.noorden
where tbfactucabecera.statusFactura='G' and
tbfactulineas.nocliente=2000 and
tbfactulineas.noemple='123456')
SET NOTARJETA='10000000000000500213'

Viewing 4 posts - 1 through 3 (of 3 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

This syntax should work. I could not test it as you did not include your table DDL and sample data. It may need tweaked slightly, but the idea is sound.

UPDATE tbfactulineas

SET NOTARJETA='10000000000000500213'

FROM tbfactulineas.NOTARJETA

inner join tbfactucabecera

on tbfactucabecera.nocliente=tbfactulineas.nocliente

and tbfactucabecera.noOrden=tbfactulineas.noorden

where tbfactucabecera.statusFactura='G'

and tbfactulineas.nocliente=2000

and tbfactulineas.noemple='123456'

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ronzo Hall of Fame Points: 3917 More actions · Answer 2

Try this:

UPDATE tbFactuLineas

SET NoTarjeta = '10000000000000500213'

FROM tbfactucabecera

INNER JOIN tbfactulineas

ON tbfactucabecera.nocliente=tbfactulineas.nocliente

AND tbfactucabecera.noOrden=tbfactulineas.noorden

WHERE tbfactucabecera.statusFactura='G'

AND tbfactulineas.nocliente=2000

AND tbfactulineas.noemple='123456'

Have Fun!
Ronzo

Teresita Castro SSCommitted Points: 1568 More actions · Answer 3

Teresita Castro

SSCommitted

Points: 1568

May 17, 2006 at 1:24 pm

#638546

Thanks!

It works!!