update using select - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

update using select

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-07-13 : 12:23:35

hi

I am not able to update using this statement: Where am i doing wrong? Thanks

Update DT
set AID = dbo.PBt.AID,
AAmount = Sum(TDS * dbo.PBt.APD)
FROM dbo.PBt INNER JOIN
dbo.PA ON dbo.PBt.AID = dbo.PA.AID INNER JOIN
dbo.PC ON dbo.PA.ARCID = dbo.PC.ARCID INNER JOIN
PST ON dbo.PBt.PID = PST.PID
where ARCat = 'allow' and PST.PID = PBt.PID
GROUP BY dbo.PST.TDS, dbo.PBt.APD,PBt.AID

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 12:27:40

are you getting an error? if so what is it?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 12:29:51

You probably need a nested query something like:


UPDATE DT
SET ...
FROM DT
    JOIN
    (
          SELECT PKCol1, PKCol2, ..., Sum(TDS * dbo.PBt.APD) AS AAmount
          FROM ...
          WHERE ...
          GROUP BY ...
    ) AS X
        ON X.PKCol1 = DT.PKCol1
       AND X.PKCol2 = DT.PKCol2
       ...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 12:40:16

update DT???
Where is DT coming from?
Is it an alias?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 12:52:21

I figure its a table, no?

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-07-13 : 12:52:43

DT is a name of a table. i have shorten the code.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-07-13 : 12:54:01

I am not using any alias for the TSQL i have posted. Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 12:54:34

are you getting an error or it is not updating the table at all? it migh tnot update because the WHERE caluse brings nothing

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-07-13 : 12:59:57

sorry

I have my code repost. It is giving me an error:

Incorrect syntax near the keyword 'GROUP'.

Update dbo.DT
set aid = dbo.PBT.aid,
aamount = Sum(tds * dbo.PBT.apd)
FROM dbo.PBT INNER JOIN
dbo.PA ON dbo.PBT.aid = dbo.PA.aid INNER JOIN
dbo.PC ON dbo.PA.arcid = dbo.PC.arcid INNER JOIN
dbo.PST ON dbo.PBT.pid = dbo.PST.pid
where arcat = 'allow' and PST.pid = PBT.pid
GROUP BY dbo.PST.tds, dbo.PBT.apd,dbo.PBT.aid

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 13:24:40

Did you try my suggestion?

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-07-13 : 13:49:26

no i have not. will try later. thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-07-14 : 02:05:18

Thanks Kristen, your solution works. Thanks again