CODEWITHSUNDEEP
mysql
Raghavendra Gupta
Raghavendra Gupta

Reputation: 355

Seeing the syntax error while updating the table with column value

I have the below query that I'm using to update the master table from the src table for NPI column. However, I'm seeing the following syntax error. Database: MySql on AWS Version: 5.7.12

UPDATE master hcp_mt
INNER JOIN 
select x.NPI from (select 
CASE 
WHEN period_end IS NULL THEN 5 
WHEN period_end IS NOT NULL THEN -30
ELSE NULL 
END  as 'NPI'
from src ) x
ON hcp_mt.r_id = x.r_id
and hcp_mt.l_id = x.l_id
and hcp_mt.p_num  =  x.p_num
SET hcp_mt.NPI = x.NPI

Error:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select x.NPI from (select 
CASE 
WHEN period_end IS NULL THEN 5 
W' at line 3

Could someone please help me with this? What could be the possible reason?

Upvotes: 0

Views: 34

Answers (1)

Barmar
Barmar

Reputation: 780798

You have an extra SELECT that's not needed, just join with the subquery.

UPDATE master hcp_mt
INNER JOIN (
    select 
        r_id, l_id, p_num,
        CASE 
            WHEN period_end IS NULL THEN 5 
            ELSE -30
        END  as 'NPI'
    from src ) x 
ON hcp_mt.r_id = x.r_id
    and hcp_mt.l_id = x.l_id
    and hcp_mt.p_num  =  x.p_num
SET hcp_mt.NPI = x.NPI

You also left out the other columns that you're joining with in the subquery.

There's no need for 3 cases in the CASE expression. Either a column is null or not, there's no third possibility.

There's also not really a need for a subquery at all. Just join with the table and put the CASE in the SET clause.

UPDATE master AS hcp_mt
JOIN src AS x
ON hcp_mt.r_id = x.r_id
    and hcp_mt.l_id = x.l_id
    and hcp_mt.p_num  =  x.p_num
SET hcp_mt.NPI = CASE 
        WHEN period_end IS NULL THEN 5
        ELSE -30
    END

This is likely to perform much better if the column you're joining on are indexed.

Upvotes: 2

Related Questions