January 20, 2009 at 11:19 pm
Hello everyone,
I can get this to run no problem, but it always says "0 records updated". I'm pretty sure it has something to do with where its looking for values...the EPool table is empty right now, I'm trying to get it populated with some queries. I just can't figure what I need to do to get it to look in the other tables to get the values needed for the update. Any assistance is appreciated!
Bet
UPDATE dbo.EPool
SET dbo.EPool.UIC = dbo.NAVMC.UIC
,dbo.EPool.TAM5 = dbo.NAVMC.TAM5
,dbo.EPool.NAVMCPO = dbo.NAVMC.PO
,dbo.EPool.OPFORTETotal = dbo.OPFORTE.OrgQTY
,PO_Over = CASE WHEN
(dbo.NAVMC.PO > dbo.OPFORTE.OrgQTY) THEN dbo.NAVMC.PO -
dbo.OPFORTE.OrgQTY ELSE '0' END
,PO_Under = CASE WHEN
(dbo.NAVMC.PO < dbo.OPFORTE.OrgQTY) THEN dbo.OPFORTE.OrgQTY -
dbo.NAVMC.PO ELSE '0' END
FROM dbo.OPFORTE RIGHT JOIN (dbo.NAVMC RIGHT JOIN tblUnit_Ref ON dbo.NAVMC.UIC = tblUnit_Ref.UIC) ON dbo.OPFORTE.MEFUIC = tblUnit_Ref.MEFUIC, dbo.EPool
WHERE (((dbo.tblUnit_Ref.DET)= 0) AND ((dbo.NAVMC.TAM5) Like '[A-E]%' And (dbo.NAVMC.TAM5) = dbo.OPFORTE_.TAM5))
January 20, 2009 at 11:31 pm
betsy.powlen (1/20/2009)
Hello everyone,I can get this to run no problem, but it always says "0 records updated". I'm pretty sure it has something to do with where its looking for values...the EPool table is empty right now, I'm trying to get it populated with some queries. I just can't figure what I need to do to get it to look in the other tables to get the values needed for the update. Any assistance is appreciated!
Bet
UPDATE dbo.EPool
SET dbo.EPool.UIC = dbo.NAVMC.UIC
,dbo.EPool.TAM5 = dbo.NAVMC.TAM5
,dbo.EPool.NAVMCPO = dbo.NAVMC.PO
,dbo.EPool.OPFORTETotal = dbo.OPFORTE.OrgQTY
,PO_Over = CASE WHEN
(dbo.NAVMC.PO > dbo.OPFORTE.OrgQTY) THEN dbo.NAVMC.PO -
dbo.OPFORTE.OrgQTY ELSE '0' END
,PO_Under = CASE WHEN
(dbo.NAVMC.PO < dbo.OPFORTE.OrgQTY) THEN dbo.OPFORTE.OrgQTY -
dbo.NAVMC.PO ELSE '0' END
FROM dbo.OPFORTE RIGHT JOIN (dbo.NAVMC RIGHT JOIN tblUnit_Ref ON dbo.NAVMC.UIC = tblUnit_Ref.UIC) ON dbo.OPFORTE.MEFUIC = tblUnit_Ref.MEFUIC, dbo.EPool
WHERE (((dbo.tblUnit_Ref.DET)= 0) AND ((dbo.NAVMC.TAM5) Like '[A-E]%' And (dbo.NAVMC.TAM5) = dbo.OPFORTE_.TAM5))
Instead of updating, perform an INSERT
Insert into dbo.EPool (col1,col2........)
select col1, col2.....
from your other tables with joins and other conditions...
January 20, 2009 at 11:42 pm
Thanks, Pradeep. I was trying to find a way to make something I can run more than once, though. We're still developing and cleansing the underlying data and I know I'll be doing this again. I need the table updated for testing and I keep having to remake it. If I could get a template I could make this for all the tables I have to update from other tables. Perhaps there is a better way, I'm really new at this and struggling...I guess I should have posted in SQL newbies!
January 21, 2009 at 12:01 am
I think i'm not clear on your exact requirement. What i understand is you need to populate this table (and may be others) at regular intervals for testing purpose.
If this is so, you can truncate the tables and re-execute the insert query.
note that you use UPDATE statement to change existing data in the table and INSERT to add new rows to the table.
Refer books online for details.
January 21, 2009 at 12:16 am
UPDATE dbo.EPool
SET dbo.EPool.UIC = dbo.NAVMC.UIC
,dbo.EPool.TAM5 = dbo.NAVMC.TAM5
,dbo.EPool.NAVMCPO = dbo.NAVMC.PO
,dbo.EPool.OPFORTETotal = dbo.OPFORTE.OrgQTY
,PO_Over = CASE WHEN
(dbo.NAVMC.PO > dbo.OPFORTE.OrgQTY) THEN dbo.NAVMC.PO -
dbo.OPFORTE.OrgQTY ELSE '0' END
,PO_Under = CASE WHEN
(dbo.NAVMC.PO < dbo.OPFORTE.OrgQTY) THEN dbo.OPFORTE.OrgQTY -
dbo.NAVMC.PO ELSE '0' END
FROM dbo.OPFORTE RIGHT JOIN (dbo.NAVMC RIGHT JOIN tblUnit_Ref ON dbo.NAVMC.UIC = tblUnit_Ref.UIC) ON dbo.OPFORTE.MEFUIC = tblUnit_Ref.MEFUIC, dbo.EPool
WHERE (((dbo.tblUnit_Ref.DET)= 0) AND ((dbo.NAVMC.TAM5) Like '[A-E]%' And (dbo.NAVMC.TAM5) = dbo.OPFORTE_.TAM5))
Before running update statement, first check u have any records matching your query.
select * from
FROM dbo.OPFORTE RIGHT JOIN (dbo.NAVMC RIGHT JOIN tblUnit_Ref ON dbo.NAVMC.UIC = tblUnit_Ref.UIC) ON dbo.OPFORTE.MEFUIC = tblUnit_Ref.MEFUIC, dbo.EPool
WHERE (((dbo.tblUnit_Ref.DET)= 0) AND ((dbo.NAVMC.TAM5) Like '[A-E]%' And (dbo.NAVMC.TAM5) = dbo.OPFORTE_.TAM5))[/quote]
Check if the above query giving any results. If its giving any results it should update those records when u run the update statement.
January 21, 2009 at 12:20 am
brainy (1/21/2009)
select * fromFROM dbo.OPFORTE RIGHT JOIN (dbo.NAVMC RIGHT JOIN tblUnit_Ref ON dbo.NAVMC.UIC = tblUnit_Ref.UIC) ON dbo.OPFORTE.MEFUIC = tblUnit_Ref.MEFUIC, dbo.EPool
WHERE (((dbo.tblUnit_Ref.DET)= 0) AND ((dbo.NAVMC.TAM5) Like '[A-E]%' And (dbo.NAVMC.TAM5) = dbo.OPFORTE_.TAM5))
[/code]
Check if the above query giving any results. If its giving any results it should update those records when u run the update statement.
[/quote]
Checking for results the query you mentioned is fine. However, ePOOL table doesnot contain any records, you can only Insert into that table and not update.
January 21, 2009 at 12:31 am
Checking for results the query you mentioned is fine. However, ePOOL table doesnot contain any records, you can only Insert into that table and not update
If it is not having any records, how can you update the table. you can only insert records in to that table.
January 21, 2009 at 12:43 am
Yes. Thats what i wrote. The first post in the thread says epool table is empty:)
January 21, 2009 at 6:46 am
Thank you everyone, I appreciate all the responses. Obviously I wasn't thinking clearly about what it was I was actually trying to do now. My mind was stuck in what I wanted to do later.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply