CODEWITHSUNDEEP
phpmysqlsubquery
roxor
roxor

Reputation: 11

how to resolve this query below and what is the purpose of using IN operator

update radcheck SET radcheck.attribute = 'rejected' where radcheck.username IN (select username from radpostauth where radpostauth.username = $mobile AND radpostauth.authdate < now() - 120)");

i want my query to update column "attribute" of a username in radcheck table when the same username in radpostauth table has value "reject" in his reply column i need to first update radpostauth.reply = 'reject' on the basis of radpostauth.authdate

Upvotes: 0

Views: 139

Answers (3)

Dhaval Bhavsar
Dhaval Bhavsar

Reputation: 495

Hi can you please use JOIN with update query

    update radcheck 
JOIN radpostauth ON radpostauth.authdate < now() - 120
SET radpostauth.reply = 'reject'


update radcheck 
JOIN radpostauth ON (radpostauth.username = radcheck.username)  AND (radpostauth.authdate < now() - 120)
SET radcheck.attribute = 'rejected' 
where radpostauth.reply = 'reject';

Upvotes: 1

duttbhavik
duttbhavik

Reputation: 36

Please use join in mysql query because better performance use join instead of inner query 

update radcheck as rc
JOIN radpostauth asrpa ON (rpa.username = rc.username)  AND (rpa.authdate < now() - 120)
SET rc.attribute = 'rejected' 
where rpa.reply = 'reject';

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133380

based on your description you shuold subseelect the username where adpostauth table has value "reject" in his reply column

update radcheck 
SET radcheck.attribute = 'rejected' 
where radcheck.username IN (select username 
                        from radpostauth
                        where radpostauth.reply  = 'reject' ");

Upvotes: 0

Related Questions