Dreamweaver - Update with subquery (Select)

Author

Update with subquery (Select)

Aleks

2004-08-08, 4:14 am

I need to run this update statement on a MS SQL 2000

Update
Cases
set archived = 2

But only on records found with this query ...

Select * from
Cases
Inner join processstep
ON cases.PROCESSSTEP = processstep.ProcessstepID
where processstep.Processtep LIKE '%closed%'

How can I do this ?

Aleks

Kindler Chase

2004-08-08, 7:14 pm

Aleks wrote:
> I need to run this update statement on a MS SQL 2000
>
>
> Update
> Cases
> set archived = 2
>
> But only on records found with this query ...
>
> Select * from
> Cases
> Inner join processstep
> ON cases.PROCESSSTEP = processstep.ProcessstepID
> where processstep.Processtep LIKE '%closed%'
>
> How can I do this ?
>
> Aleks

UPDATE Cases

SET archived = 2

WHERE
Cases.YourPK IN(
SELECT Cases.YourPK
FROM Cases
INNER JOIN processstep
ON cases.PROCESSSTEP = processstep.ProcessstepID
WHERE processstep.Processtep LIKE '%closed%'
)

You set up a Where clause to grab all the rows you want to update by
filtering for their Primary Keys and using the IN operator. YourPK refers to
the Primary Key in the Cases table.

--
kindler chase
http://www.ncubed.com
Home of SuperInvoice: The Online Invoicing Application.
Organize your billing process and impress your clients.

news://news.ncubed.com/support
n3 Support Group

Kindler Chase

2004-08-13, 7:14 am