CODEWITHSUNDEEP
mysqlsqljoomla
ricardo
ricardo

Reputation: 215

Update a column in MySQL table if only the values are empty or NULL

I had previously applied this query...which works perfectly and was answered by one of the fellow members of this forum 

UPDATE
    jos_jbjobs_jobseeker a
    INNER JOIN jos_users b ON a.email = b.email
SET
    a.user_id = b.id

Now i want to use the same query adding one more condition... i.e

Set a.user_id = b.id only if a.user_id is empty ,,

can i apply this :

if a.user_id = '' SET a.user_id = b.id ;

?

Upvotes: 7

Views: 27832

Answers (3)

MISSIRIA
MISSIRIA

Reputation: 456

Try this code SQL native it's work for me very well :

UPDATE table 
SET field = 'New value'
WHERE field 
IS NULL
OR field = ''

Update just NULL value or EMPTY.

Upvotes: 1

Ankit Sharma
Ankit Sharma

Reputation: 4071

Use this

UPDATE
    jos_jbjobs_jobseeker a
    INNER JOIN jos_users b ON a.email = b.email
SET
    a.user_id = b.id
WHERE a.id ='';

If id have null values too then use this-

UPDATE
    jos_jbjobs_jobseeker a
    INNER JOIN jos_users b ON a.email = b.email
SET
    a.user_id = b.id
WHERE a.id is null or a.id ='';

Upvotes: 1

Teja
Teja

Reputation: 13524

UPDATE
    jos_jbjobs_jobseeker a
    INNER JOIN jos_users b ON a.email = b.email
SET
    a.user_id = b.id
WHERE a.id IS NULL OR LENGTH(a.id)=0;

Upvotes: 16

Related Questions