CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Low Chee Mun
Low Chee Mun

Reputation: 610

Update Parents table status

what i want trying to do is to update my child table when my parents status is "Deleted"

Parents Table

-------------
PID | CID |Pstatus
1   |  1  | Deleted
2   |  1  | Active
3   |  2  | Deleted
4   |  2  | Deleted

the logic behind is like below

IF CID = 1 and PsTatus = deleted

Update ChildTableStatus
Set Status = 'Deleted'
Where CID = 1

Else

cannot update childtablestatus to deleted due to there are active records

how to perform a looping check over here?

Upvotes: 1

Views: 75

Answers (3)

Bedabrata
Bedabrata

Reputation: 15

I think the following DML should be better than using the nested queries such as EXISTS or IN as they bring down the performance.

Update p1
Set p1.Status = 'Deleted'
FROM Parents p1 JOIN Parents p2
ON p1.pid = p2.cid
Where p2.status = 'Deleted'
  • This is assuming you are trying to do the update on same table. Otherwise, just change the table name in FROM clause.

Upvotes: 1

Vijay
Vijay

Reputation: 8461

try out this..

Update ChildTableStatus
Set Status = 'Deleted'
Where CID = (Select CID from Parents where Pstatus = 'deleted')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

I am assuming that "ChildTableStatus" actually refers to the same table. You are just looking for the right filtering expression in that case:

update parents
    set status = 'deleted'
    where exists (select 1 from parents p2 where p2.cid = p.pid and p2.status = 'deleted')

Upvotes: 2

Related Questions