Prev: Question on SQL Server 2008 and MS Access 2003
Next: Code and Creation 74116
From: OceanDeep via SQLMonster.com on 15 Jan 2010 12:52
I am reviewing some old code and the below query is the one I like not to use
subquer. Is there a different way to write this query?


update tblProduct
set ProductCatory_name = 'Hard'
where
ProductCat_id =400 or ProductCat_id in (
select pdt.productCat_ID from tblProduct as Pdt where
pdt.ProductSource_ID = 400 and pdt.Manager_ID >0)


OD

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: Tom Moreau on 15 Jan 2010 14:22
What's wrong with using a subquery?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message
news:a2285b8f7e9c3(a)uwe...
I am reviewing some old code and the below query is the one I like not to
use
subquer. Is there a different way to write this query?


update tblProduct
set ProductCatory_name = 'Hard'
where
ProductCat_id =400 or ProductCat_id in (
select pdt.productCat_ID from tblProduct as Pdt where
pdt.ProductSource_ID = 400 and pdt.Manager_ID >0)


OD

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: --CELKO-- on 15 Jan 2010 15:07
>>  Is there a different way to write this query? <<

It is not a query; it is an update. And, yes, you can always write a
statement several ways. You messed up the data element names. There
is no such thing as a "<anything>_category_id" in a valid data model
-- an attribute can be a category or an identifier but not both.

Give us some DDL and maybe we can do something.

From: OceanDeep via SQLMonster.com on 15 Jan 2010 15:32
I didn't mess up the data element names. They may not be consistent with
naming but they are what they are. I am not sure what you are talking about.
Anyway, here is the table structure.

tblProduct
ProductCat_ID int not null primary key
ProductCatory_name Varchar(255) not null
ProductSource_ID int not null
Manager_ID int null

--CELKO-- wrote:
>>>  Is there a different way to write this query? <<
>
>It is not a query; it is an update. And, yes, you can always write a
>statement several ways. You messed up the data element names. There
>is no such thing as a "<anything>_category_id" in a valid data model
>-- an attribute can be a category or an identifier but not both.
>
>Give us some DDL and maybe we can do something.

--
Message posted via http://www.sqlmonster.com

From: Plamen Ratchev on 15 Jan 2010 17:09
Try this:

UPDATE tblProduct
SET ProductCatory_name = 'Hard'
WHERE ProductCat_id = 400
OR (ProductSource_ID = 400 AND Manager_ID > 0);

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Question on SQL Server 2008 and MS Access 2003
Next: Code and Creation 74116