How do I use this update multiple columns?

You could include the additional columns in your SET statement.

Tried this and it didn't work.

update tasks_master_tracker_idb idb 
SET
idb.CLIENT_CONTACT, idb.COMPLEXITY, idb.CREATED_BY = t.CREATED_BY,
idb.CUSTID, idb.DIRECTION, idb.INTEGRATION, idb.INTEGRATION_NAME, idb.INTEGRATION_STATUS, 
idb.INTEGRATION_TYPE, idb.VENDOR, idb.INTEGRATION_DESCRIPTION, idb.MODIFIED_BY, 
idb.MODIFIED_DATE, idb.MONDAY, idb.TUESDAY, idb.WEDNESDAY, idb.THURSDAY, idb.FRIDAY, idb.SATURDAY, idb.SUNDAY= (
   SELECT t.CLIENT_CONTACT, t.COMPLEXITY, t.CREATED_BY, t.CUSTID, t.DIRECTION,
   t.INTID, t.NAME, t.STATUS, t.PROJ_TYPE, t.VENDOR, r.DESCRIPTION, r.MODIFIED_BY, r.MODIFIED_DT,
   tsk.MONDAY, tsk.TUESDAY, tsk.WEDNESDAY, tsk.THURSDAY, tsk.FRIDAY, tsk.SATURDAY, tsk.SUNDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001' 
)
WHERE
idb.CUSTID='PRU01'
AND
idb.INTEGRATION_ID='INT001' 

Select allOpen in new window

What am I missing?

Paul!

Good to hear from you!

I was able to get things to work using something that may be clunky, but it gets the job done...

update tasks_master_tracker_idb idb 
SET
idb.CLIENT_CONTACT = (SELECT t.CLIENT_CONTACT
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.COMPLEXITY = (SELECT t.COMPLEXITY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.CREATED_BY = (SELECT t.CREATED_BY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'), 
idb.CUSTID = 'PRU01', 
idb.DIRECTION = (SELECT t.DIRECTION
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'), 
idb.INTEGRATION_ID = 'INT001',
idb.INTEGRATION_NAME = (SELECT t.NAME
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'), 
idb.INTEGRATION_STATUS = (SELECT t.STATUS
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'), 
idb.INTEGRATION_TYPE = (SELECT t.PROJ_TYPE
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'), 
idb.VENDOR = (SELECT t.VENDOR
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.INTEGRATION_DESCRIPTION= (SELECT r.DESCRIPTION
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),  
idb.MODIFIED_BY = (SELECT r.MODIFIED_BY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),  
idb.MODIFIED_DATE = (SELECT r.MODIFIED_BY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),  
idb.MODIFIED_DATE = (
   SELECT r.MODIFIED_DT
      FROM
      tasks t
      LEFT JOIN requests r
      ON t.ID = r.TASK
      LEFT JOIN tasks_ams tsk
      ON t.ID = tsk.RID
      WHERE 
      t.CUSTID='PRU01'
      AND
      t.INTID='INT001'
      ) , 
idb.MONDAY = (SELECT tsk.MONDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),  
idb.TUESDAY = (SELECT tsk.TUESDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.WEDNESDAY = (SELECT tsk.WEDNESDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.THURSDAY = (SELECT tsk.THURSDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.FRIDAY = (SELECT tsk.FRIDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.SATURDAY = (SELECT tsk.SATURDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001'),
idb.SUNDAY = (SELECT tsk.SUNDAY
   FROM
   tasks t
   LEFT JOIN requests r
   ON t.ID = r.TASK
   LEFT JOIN tasks_ams tsk
   ON t.ID = tsk.RID
   WHERE 
   t.CUSTID='PRU01'
   AND
   t.INTID='INT001')
WHERE
idb.CUSTID='PRU01'
AND
idb.INTEGRATION_ID='INT001' 
AND
idb.INTEGRATION_NAME = 'INT001_ADSI_FeeDeduction_SI_Inbound'

Select allOpen in new window

...but here's the thing:

MODIFIED_DT can't be NULL.

I tried to write a CASE statement, but I'm stuck on how to retrieve today's date.

I was going with SELECT(CURDATE); but it didn't seem to work in context.

How can I do this:

idb.MODIFIED_DT = (SELECT
   case
   when
      r.MODIFIED_DT IS NULL
   then
      SELECT CURDATE()
   else
      r.MODIFIED_DT
   end
      FROM
      tasks t
      LEFT JOIN requests r
      ON t.ID = r.TASK
      LEFT JOIN tasks_ams tsk
      ON t.ID = tsk.RID
      WHERE
      t.CUSTID='PRU01'
      AND
      t.INTID='INT001')

idb.MODIFIED_DT = COALESCE( (your subquery here), GETDATE() )

Morning, Julian...

Paul!
idb.MODIFIED_DT = COALESCE( (your subquery here), GETDATE() ) got it done!

It's one slow moving query, though. I'm meeting with my Team Lead this am. The fact that I'm not showing up empty handed and have a working method is huge. But I'll see what he says about the possibility of optimizing it using your "common table expression" approach and we'll go from there!

Thanks for your help!

Guys!

This is what I was able to get to work. What I did was go back and construct an index that then allowed the "ON DUPLICATE KEY UPDATE" to work so I didn't have to go back and identify a specific row via multiple SELECTS.

Here's what did the trick:

function updateTasksMasteriDB($params){

            $sql_1 = "INSERT into tasks_master_tracker_idb (
         `CLIENT_CONTACT`, `COMPLEXITY`, `CREATED_BY`, `CUSTID`, `DIRECTION`,
         `INTEGRATION_ID`, `INTEGRATION_NAME`, `INTEGRATION_STATUS`, `INTEGRATION_TYPE`, `VENDOR`, `INTEGRATION_DESCRIPTION`, `MODIFIED_BY`, `MODIFIED_DATE`,
         `MONDAY`, `TUESDAY`, `WEDNESDAY`, `THURSDAY`, `FRIDAY`, `SATURDAY`, `SUNDAY`
         )
         SELECT t.CLIENT_CONTACT, t.COMPLEXITY, t.CREATED_BY, t.CUSTID, t.DIRECTION,
            t.INTID, t.NAME, t.STATUS, t.PROJ_TYPE, t.VENDOR, r.DESCRIPTION, r.MODIFIED_BY, r.MODIFIED_DT,
            tsk.MONDAY, tsk.TUESDAY, tsk.WEDNESDAY, tsk.THURSDAY, tsk.FRIDAY, tsk.SATURDAY, tsk.SUNDAY
            FROM
            tasks t
            LEFT JOIN requests r
            ON t.ID = r.TASK
            LEFT JOIN tasks_ams tsk
            ON t.ID = tsk.RID
            WHERE 
            t.CUSTID='{$params['custid']}'
            AND
            t.INTID='{$params['intid']}'
         ON DUPLICATE KEY UPDATE
         `CLIENT_CONTACT` = t.CLIENT_CONTACT,
         `COMPLEXITY` = t.COMPLEXITY,
         `CREATED_BY` = t.CREATED_BY,
         `CUSTID` = t.CUSTID,
         `DIRECTION` = t.DIRECTION,
         `INTEGRATION_ID` = t.INTID,
         `INTEGRATION_NAME` = t.NAME,
         `INTEGRATION_STATUS` = t.STATUS,
         `INTEGRATION_TYPE` = t.PROJ_TYPE,
         `VENDOR` = t.VENDOR,
         `INTEGRATION_DESCRIPTION` = r.DESCRIPTION,
         `MODIFIED_BY` = r.MODIFIED_BY,
         `MODIFIED_DATE` = r.MODIFIED_DT,
         `MONDAY` = tsk.MONDAY,
         `TUESDAY` = tsk.TUESDAY,
         `WEDNESDAY` = tsk.WEDNESDAY,
         `THURSDAY` = tsk.THURSDAY,
         `FRIDAY` = tsk.FRIDAY,
         `SATURDAY` = tsk.SATURDAY,
         `SUNDAY` = tsk.SUNDAY";
            
         $this->db->query($sql_1);
        }

Select allOpen in new window

Thanks for your help! I'm taking notes every time I engage EE so none of your suggestions fell on deaf ears!