How to replace different occurances in SQL?

Question

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?

No conditions, it is just a requirement. It has to be in the select query. — jaji18
– jaji18, Commented Apr 25, 2014 at 13:16
No what I am asking is when do you want to not return 'yes'. When would you return 'no'? — geoand
– geoand, Commented Apr 25, 2014 at 13:17

Michael · Accepted Answer · 2014-04-25 13:17:32Z

2

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)

answered Apr 25, 2014 at 13:17

Michael

4771 gold badge5 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jaji18 Over a year ago

Yes, that works, I was just thinking too much on the REPLACE() function.

rory.ap · Accepted Answer · 2014-04-25 13:13:06Z

0

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)

answered Apr 25, 2014 at 13:13

rory.ap

35.7k15 gold badges104 silver badges199 bronze badges

1 Comment

jaji18 Over a year ago

The idea is correct but I want to keep the select. So I make the replace inside the select query.

Collectives™ on Stack Overflow

How to replace different occurances in SQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related