Updating table 1 using data from table 2

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

Transact-SQL (2005)

Updating table 1 using data from table 2

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-03 : 13:20:18

Here is my scenario.....

POAmendmentProject table
-----------------------------
PurchaseOrderID POAmendmentID ProjectPhaseId FiscalYear cost
4 10 12 2008 0.00
4 10 12 2009 11.00
4 10 12 2010 10.00
4 10 12 2011 0.00
4 10 12 2012 0.00
4 10 13 2008 0.00
4 10 13 2009 11.00
4 10 13 2010 0.00
4 10 13 2011 0.00
4 10 13 2012 0.00

POProject table
-----------------
PurchaseOrderID ProjectPhaseID FiscalYear Cost
4 12 2007 200000.00
4 12 2010 25000.00
4 10 2007 200000.00
4 10 2008 40000.00
4 10 2009 10000.00
4 13 2007 25000.00
4 13 2008 7000.00

I want to update POProject table using the data contained in POAmendmentProject table. The matching keys between the 2 tables will be PurchaseOrderId, ProjectPhaseId and Fiscal Year. For each row in POAmendmentProject table, find the mataching row in POProject table and update the Cost value by summing up the cost values in both the rows. For e.g.: If cost is $10 in ProjectAmendment table and cost is $50 in POProject table, Update the cost value to $60 (10 +50) in POProject table. If the matching row is not found in POProject table, insert a new record (this will be copied from POAmendmentProject table to POProject table.

Is this possible to do in SQL?
Please help

Thanks

rum23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 13:38:21

[code]UPDATE pap
SET pap.cost=pap.cost+pp.Cost
FROM POAmendmentProject pap
INNER JOIN POProject pp
ON pp.PurchaseOrderID =pap.PurchaseOrderID
AND pp.ProjectPhaseID =pap.ProjectPhaseID
AND pp.FiscalYear =pap.FiscalYear

INSERT INTO POProject
SELECT pap.PurchaseOrderID,
pap.ProjectPhaseID,
pap.FiscalYear,
pap.Cost
FROM POAmendmentProject pap
LEFT JOIN POProject pp
ON pp.PurchaseOrderID =pap.PurchaseOrderID
AND pp.ProjectPhaseID =pap.ProjectPhaseID
AND pp.FiscalYear =pap.FiscalYear
WHERE pp.PurchaseOrderID IS NULL
AND pp.ProjectPhaseID IS NULL
AND pp.FiscalYear IS NULL[/code]

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-03 : 14:21:14

Thanks a lot for the quick reply.

I am trying to test the first update statement. When I execute the update sql mentioned below, I get x's rows affected. However, in the table, the cost is not reflecting the new values. What could be wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 14:26:09

quote:
Originally posted by rum23

Thanks a lot for the quick reply.

I am trying to test the first update statement. When I execute the update sql mentioned below, I get x's rows affected. However, in the table, the cost is not reflecting the new values. What could be wrong?

show your full update code. Also are you running it as a transaction?

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-03 : 14:33:17

UPDATE POProject
SET POProject.Cost = (POProject.Cost + POAmendmentProject.Cost)
FROM POProject INNER JOIN POAmendmentProject
ON POAmendmentProject.ProjectPhaseId = POProject.ProjectPhaseID
AND POAmendmentProject.FiscalYear = POProject.FiscalYear
AND POProject.PurchaseOrderId = POAmendmentProject.PurchaseOrderId

I am running this from SQL server 2005 query analyzer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 14:39:00

quote:
Originally posted by rum23

UPDATE POProject
SET POProject.Cost = (POProject.Cost + POAmendmentProject.Cost)
FROM POProject INNER JOIN POAmendmentProject
ON POAmendmentProject.ProjectPhaseId = POProject.ProjectPhaseID
AND POAmendmentProject.FiscalYear = POProject.FiscalYear
AND POProject.PurchaseOrderId = POAmendmentProject.PurchaseOrderId

I am running this from SQL server 2005 query analyzer.

this looks fine.then why do you think values didnt cange?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-03 : 14:40:40

[quote]Originally posted by rum23

UPDATE POProject
SET POProject.Cost = (POProject.Cost + POAmendmentProject.Cost)
FROM POProject INNER JOIN POAmendmentProject
ON POAmendmentProject.ProjectPhaseId = POProject.ProjectPhaseID
AND POAmendmentProject.FiscalYear = POProject.FiscalYear
AND POProject.PurchaseOrderId = POAmendmentProject.PurchaseOrderId

I am running this from SQL server 2005 query analyzer.
[/quote

It should be other table.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-03 : 14:43:46

It should be
UPDATE PP instead of UPDATE PAP

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-03 : 14:44:20

update POProject
set cost = a.cost + b.cost
from POProject a, POAmendmentProject b
where a.purchaseorderid = b.purchaseorderid
and a.projectphaseid = b.projectphaseid
and a.fiscalyear = b.fiscalyear;

insert into POProject
select Purchaseorderid, projectphaseid, fiscalyear,cost from POAmendmentProject b
where not exists (select 1 from poproject a where a.purchaseorderid = b.purchaseorderid
and a.projectphaseid = b.projectphaseid
and a.fiscalyear = b.fiscalyear)

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-03 : 14:45:12

I am not sure why its not updating. sodeep, I'm not sure what you mean by the "other table". I do want to update POProject table using the values from POAmendmentProject table.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-03 : 14:48:18

quote:
Originally posted by rum23

I am not sure why its not updating. sodeep, I'm not sure what you mean by the "other table". I do want to update POProject table using the values from POAmendmentProject table.

You do want to update POProject table BUT visakh16 was updating POAmendmentProject.
sodeep pointed out that you want to UPDATE POPrject instead of the alias PP it is less confusing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 14:49:36

quote:
Originally posted by rum23

I am not sure why its not updating. sodeep, I'm not sure what you mean by the "other table". I do want to update POProject table using the values from POAmendmentProject table.

nope i had aliases the other way around. thats what they meant. But as you've not used aliases your update is correct.

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-03 : 15:57:45

UPDATE pap
SET pap.cost=pap.cost+pp.Cost
FROM POAmendmentProject pap
INNER JOIN POProject pp
ON pp.PurchaseOrderID =pap.PurchaseOrderID
AND pp.ProjectPhaseID =pap.ProjectPhaseID
AND pp.FiscalYear =pap.FiscalYear

is not working either. Please help!

If I simply do Update POProject Set Cost = Cost + 1, this sql works. Not sure how to solve this.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-03 : 17:06:32

First of all PAP is an alias for POAmendmentProject, PP is an alias for POPROJECT
Do not UPDATE PAP, use the real table name instead.
Copy the following:

UPDATE POProject 
SET pap.cost=pap.cost+pp.Cost
FROM POAmendmentProject pap
INNER JOIN POProject pp
ON pp.PurchaseOrderID =pap.PurchaseOrderID 
AND pp.ProjectPhaseID =pap.ProjectPhaseID 
AND pp.FiscalYear =pap.FiscalYear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 07:15:53

quote:
Originally posted by hanbingl

First of all PAP is an alias for POAmendmentProject, PP is an alias for POPROJECT
Do not UPDATE PAP, use the real table name instead.
Copy the following:

UPDATE POProject
SET pap.cost=pap.cost+pp.Cost
FROM POAmendmentProject pap
INNER JOIN POProject pp
ON pp.PurchaseOrderID =pap.PurchaseOrderID
AND pp.ProjectPhaseID =pap.ProjectPhaseID
AND pp.FiscalYear =pap.FiscalYear

again aliases are wrong. remove alases from all

UPDATE POProject 
SET POProject.cost=POProject.cost+POAmendmentProject.Cost
FROM POAmendmentProject 
INNER JOIN POProject 
ON POProject.PurchaseOrderID =POAmendmentProject.PurchaseOrderID 
AND POProject.ProjectPhaseID =POAmendmentProject.ProjectPhaseID 
AND POProject.FiscalYear =POAmendmentProject.FiscalYear

or use correct aliases

UPDATE pp
SET pp.cost=pp.cost+pap.Cost
FROM POAmendmentProject pap
INNER JOIN POProject pp
ON pp.PurchaseOrderID =pap.PurchaseOrderID 
AND pp.ProjectPhaseID =pap.ProjectPhaseID 
AND pp.FiscalYear =pap.FiscalYear

anf if both are not working make sure
1.you dont have any enabled update triggers on POProject.
2. you're checking the correct table's results (table in the correct schema and not in same table which is existing in someother schema. whatever changes you do will be reflected only in tables of your default schema unless you're the db owner)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources