Update Query using multiple table in Join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Query using multiple table in Join

Author

Topic

simi28
Starting Member

27 Posts

Posted - 2008-10-02 : 08:38:55

Hi,

Will the update query work for updating the value in pct column of Table a.

Update a Set a. pct=PP. PCT
From
p,a,pp,ap,ph,ba
Where
p.pid = a.pid
and ap.aid = a.aid
and pp.pid = p.pid
and ba.bid = p.bid
and ph.productid = pp.productid
and ph.pdscr = ap.pdscr
and pp.pct != ap.pct
and pp.pct != 0
and ap.pct = 100
and pp.pct != a.pct
and a.status != 4
and p.statusid != 6
and year(a.enddate) in (2006,2007,2008,2009)

if not then,please let me know what will be the correct synatx for the same.

Seema

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 08:53:44

It not?
Haven't you tried your code above?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 09:23:50

[code]UPDATE yak
SET yak.PCT = pp.PCT
FROM a AS yak
INNER JOIN p ON p.PID = yak.PID
AND p.STATUSID <> 6
INNER JOIN pp ON pp.PID = p.PID
AND pp.PCT <> 0
INNER JOIN ap ON ap.AID = yak.AID
AND ap.PCT = 100
INNER JOIN ph ON ph.PRODUCTID = pp.PRODUCTID
INNER JOIN ba ON ba.BID = p.BID
WHERE AND yak.STATUS <> 4
AND yak.ENDDATE >= '20060101'
AND yak.ENDDATE < '20100101'
AND ph.PDSCR = ap.PDSCR
AND pp.PCT <> ap.PCT
AND pp.PCT <> a.PCT[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources