CODEWITHSUNDEEP
mysql
Red Bottle
Red Bottle

Reputation: 3080

MYSQL : IF EXISTS

I am trying to update a table called jdwf_orders_main if the value of jaj_jno is present in jdwf_alien_jobs table.

I am trying to do it using IF EXISTS but I can't seem to get the syntax right. What is wrong with my syntax.

IF ( EXISTS (SELECT * from jdwf_alien_jobs where jaj_jno = '7200000') ,
UPDATE jdwf_orders_main set jom_adv_name = 'IAM OP' where jom_job_no = '7200000',
UPDATE jdwf_orders_main set jom_adv_name = 'IAM Noob' where jom_job_no = '7200000');

Upvotes: 0

Views: 131

Answers (1)

axiac
axiac

Reputation: 72177

MySQL does not support the operation you tried. It provides another way to get the same result: update two or more joined tables in a single UPDATE query.

I cannot test but somethings like this should work:

UPDATE jdwf_orders_main om
    LEFT JOIN from jdwf_alien_jobs aj ON om.jom_job_no = aj.jaj_jno
SET om.jom_adv_name = IF(af.jaj_no IS NULL, 'IAM Noob', 'IAM OP')
WHERE om.jom_job_no = '7200000'

How it works

It joins the table jdwf_orders_main (aliased as om) with jdwf_alien_jobs (aliased as aj) on the om.jom_job_no = aj.jaj_jno condition.
The LEFT JOIN ensures all the rows from the left table (om) appear in the result set; if a row does not have a matching row in the right table, a row full of NULLs is used for the fields of the right table.
The WHERE clause filters only the rows that match the condition om.jom_job_no = '7200000' to be modified by the UPDATE statement.
The SET clause updates om.jom_adv_name (i.e. the column jom_adv_name from the jdwf_orders_main table) with the value computed by the IF() function.
The IF() function returns 'IAM Noob' if af.jaj_jno is NULL. This happens when for the row from om does not exist any matching row in af (see the explanation of the LEFT JOIN clause above). Otherwise (when a matching row exists), af.jaj_jno is not NULL and the IF() function returns 'IAM OP'.

Upvotes: 1

Related Questions