CODEWITHSUNDEEP
sql
jaji18
jaji18

Reputation: 55

How to replace different occurances in SQL?

I have the following query:

Select PERSON_NAME, Mobile_Nr, replace(Message, 'text 8' ,'yes') as SMSs from PERSON a
    inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
    inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
    inner join MESSAGE_ID d on c.ID=d.ID_Mobile
    where a.Person_Name not in 
    (Select PERSON_NAME from PERSON a
    inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
    inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
    inner join CALL_ID d on c.ID=d.ID_Mobile)

It returns:

      PERSON_NAME           Mobile_Nr   SMSs
First name 5 Last name 5    797900012   yes
First name 5 Last name 5    797900012   text 9
First name 5 Last name 5    797900016   text 13

How can I change all the values in SMSs to Yes in my query?

Upvotes: 0

Views: 38

Answers (2)

Michael
Michael

Reputation: 477

Not sure if I understood right. Is that what you want?

Select PERSON_NAME, Mobile_Nr, SMSs = 'Yes' from PERSON a
inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
inner join MESSAGE_ID d on c.ID=d.ID_Mobile
where a.Person_Name not in 
(Select PERSON_NAME from PERSON a
inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
inner join CALL_ID d on c.ID=d.ID_Mobile)

Upvotes: 2

rory.ap
rory.ap

Reputation: 35318

Is this what you want to do (assuming the 'Message' column is in the 'MESSAGE_ID' table)?

UPDATE MESSAGE_ID SET Message='Yes'
FROM PERSON a
inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
inner join MESSAGE_ID d on c.ID=d.ID_Mobile
where a.Person_Name not in 
(Select PERSON_NAME from PERSON a
inner join USER_MOBILE b on a.ID_PERSON=b.ID_USER
inner join MOBILE_NUMBER c on b.ID_MOBILE=C.ID
inner join CALL_ID d on c.ID=d.ID_Mobile)

Upvotes: 0

Related Questions