help converting an Access query

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)

help converting an Access query

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-02-03 : 08:51:31

Hi,
I am converting a database from Ms Access to SQL Server 2005.
I have a query in Access that I am not fully sure how to convert can someone tell me how.
thank you here is the query in Access:

UPDATE Annual, [Product Convert]
SET Annual.GroupID = LTrim([GroupID]), Annual.Product = IIf([Product Convert]![SP Product] Is Not Null,[Product Convert]![SP Product],[Annual]![Product]);

Here is what I was able to do but I know it is not fully right:

UPDATE [Annual], [Product Convert]
SET Annual.GroupID = LTrim([GroupID]),
Annual.[Product] = Case
When [Product Convert].[SP Product] Is Not Null Then
[Product Convert].[SP Product]
Else
[Annual].[Product]
End
From [Annual], [Product Convert])

ITM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 08:57:49

[code]
UPDATE a
SET a.GroupID = LTrim(pc.[GroupID]),
a.[Product] = COALESCE( pc.[SP Product] ,a.[Product])
From [Annual] a
JOIN [Product Convert] pc
ON pc.Col=a.Col
[/code]

Col represents columns by which they're linked