need to update 1 table but use 2 tables to select the right records 1

[LMGroup]

I'm using MS SQL Server 2005. I need to change the values in POItem but have to isolate the records using POItem and PurchaseOrder. I can't seem to get it right. This is the code that gives me the records I want. I can't figure out how/where to put the UPDATE and SET command to change the value of POItem.FinalInvoice. I need to switch it from 0 to 1 in those records.

USE LMCopy
SELECT PurchaseOrder.PurchaseOrderN, PurchaseOrder.DateOrdered, POItem.POItemN, POItem.FinalReceipt, POItem.FinalInvoice
FROM LMCopy.dbo.POItem POItem, LMCopy.dbo.PurchaseOrder PurchaseOrder
WHERE POItem.PurchaseOrderN = PurchaseOrder.PurchaseOrderN AND PurchaseOrder.PurchaseOrderN<>2549629 AND ((POItem.FinalReceipt<>1) AND (PurchaseOrder.DateOrdered<{ts '2010-01-01 00:00:00'}) OR (PurchaseOrder.DateOrdered<{ts '2010-01-01 00:00:00'}) AND (POItem.FinalInvoice<>1))

Any help would be appreciated.

Thanks,
Lorraine

 

[Qik3Coder]

[!]Update PoItem[/!]
[green]--SELECT PurchaseOrder.PurchaseOrderN, PurchaseOrder.DateOrdered, 
--Item.POItemN, POItem.FinalReceipt, POItem.FinalInvoice[/green]
[!]set POItem.FinalInvoice=1[/!]
FROM LMCopy.dbo.POItem POItem, LMCopy.dbo.PurchaseOrder PurchaseOrder
WHERE POItem.PurchaseOrderN = PurchaseOrder.PurchaseOrderN AND PurchaseOrder.PurchaseOrderN<>2549629 AND ((POItem.FinalReceipt<>1) AND (PurchaseOrder.DateOrdered<{ts '2010-01-01 00:00:00'}) OR (PurchaseOrder.DateOrdered<{ts '2010-01-01 00:00:00'}) AND (POItem.FinalInvoice<>1))

This is separate, but ideally you would "correct" your JOIN type to use the cleaner syntax:

FROM LMCopy.dbo.POItem POItem
JOIN LMCopy.dbo.PurchaseOrder PurchaseOrder ON
[tab]POItem.PurchaseOrderN = PurchaseOrder.PurchaseOrderN 
[tab]AND PurchaseOrder.PurchaseOrderN<>2549629 
[tab]AND ((POItem.FinalReceipt<>1) 
[tab]AND (PurchaseOrder.DateOrdered<{ts '2010-01-01 00:00:00'}) OR (PurchaseOrder.DateOrdered<{ts '2010-01-01 00:00:00'}) AND (POItem.FinalInvoice<>1))

Lod


You've got questions and source code. We want both!

 

[gmmastros]

That's exactly what I was going to "say". You beat me by a couple minutes.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

Gmm,
I'm just glad to get an easy one out of the way.

LMGroup,
For any complex update like this, I normally run a select first, and then add the update in after, in a similar fashion as displayed above.

Sometimes it's easier to do a single column select and then do a IN() such as:

Update tableA
Set col1=42
FROM tableA
WHERE ID in(
[tab]Select ID 
[tab]from tableA 
[tab]JOIN tableB On 
[tab]tableA.Col1 = 41 
[tab]and tableB.Col2 = 57
)

Lod

You've got questions and source code. We want both!

 

[LMGroup]

Thank you to both of you!

I haven't needed to do much with SQL statements in the past beyond some relatively twisted SELECT statements. I can see a change coming in my role and I'm going to have to learn a whole lot more about SQL programming in the near future. This site and your help are invaluable tools for me.

Thanks again.