select
case
when
(select count(*) from tasks t where t.CUSTID='DPR01' and t.INTID='INT0001'>0)
then
UPDATE tasks_master_tracker_idb SET
CLIENT_CONTACT = (SELECT t.CLIENT_CONTACT FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
COMPLEXITY = (SELECT t.COMPLEXITY FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
CREATED_BY = (SELECT t.CREATED_BY FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
CUSTID = (SELECT t.CUSTID FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
DIRECTION = (SELECT t.DIRECTION FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
INTEGRATION_ID = (SELECT t.INTID FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
INTEGRATION_NAME = (SELECT t.NAME FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
INTEGRATION_STATUS = (SELECT t.STATUS FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
INTEGRATION_TYPE = (SELECT t.PROJ_TYPE FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001'),
VENDOR = (SELECT t.VENDOR FROM tasks t WHERE t.CUSTID='DPR01' AND t.INTID='INT0001')
WHERE
CUSTID='DPR01'
AND
INTEGRATION_ID='INT001'
else
INSERT into tasks_master_tracker_idb (
CLIENT_CONTACT, COMPLEXITY, CREATED_BY, CUSTID, DIRECTION,
INTEGRATION_ID, INTEGRATION_NAME, INTEGRATION_STATUS, INTEGRATION_TYPE, VENDOR
)
SELECT t.CLIENT_CONTACT, t.COMPLEXITY, t.CREATED_BY, t.CUSTID, t.DIRECTION,
t.INTID, t.NAME, t.STATUS, t.PROJ_TYPE, t.VENDOR
FROM
tasks t
WHERE
t.CUSTID='DPR01'
AND
t.INTID='INT0001'
end
I'm getting an error that says, "Error Code: 1064. 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 'UPDATE tasks_master_tracker_idb SET CLIENT_CONTACT = (SELECT t.CLIENT_CONTA' at line 6ASKER
alter table tasks_master_tracker_idb add primary key(CUSTID , INTID );
ASKER
ASKER
INSERT into tasks_master_tracker_idb (
CLIENT_CONTACT, COMPLEXITY, CREATED_BY, CREATED_DATE, CUSTID, DIRECTION,
INTEGRATION_ID, INTEGRATION_NAME, INTEGRATION_STATUS, INTEGRATION_TYPE, VENDOR
)
SELECT t.CLIENT_CONTACT, t.COMPLEXITY, t.CREATED_BY, t.CREATED_DT, t.CUSTID, t.DIRECTION,
t.INTID, t.NAME, t.STATUS, t.PROJ_TYPE, t.VENDOR
FROM
tasks t
WHERE
t.CUSTID='{$params['custid']}'
AND
t.INTID='{$params['intid']}'
ON DUPLICATE KEY UPDATE
CLIENT_CONTACT = (SELECT t.CLIENT_CONTACT FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
COMPLEXITY = (SELECT t.COMPLEXITY FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
CREATED_BY = (SELECT t.CREATED_BY FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
CREATED_DATE = (SELECT t.CREATED_DT FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
CUSTID = (SELECT t.CUSTID FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
DIRECTION = (SELECT t.DIRECTION FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
INTEGRATION_ID = (SELECT t.INTID FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
INTEGRATION_NAME = (SELECT t.NAME FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
INTEGRATION_STATUS = (SELECT t.STATUS FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
INTEGRATION_TYPE = (SELECT t.PROJ_TYPE FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}'),
VENDOR = (SELECT t.VENDOR FROM tasks t WHERE t.CUSTID='{$params['custid']}' AND t.INTID='{$params['intid']}');
...or will the new Primary Key be automatically included in the evaluation of the INSERT and recognize it accordingly?
slight! This smells really good: alter table tasks_master_tracker_idb add primary key(CUSTID , INTID );CAVEAT:
for example, on 1/1/2021 you get an 'update' (because of the duplicate key) that changes the vendor to 'Fred', then on 1/2/2021 you get an another duplicate that updates to 'Barney'.This is also a typical ETL problem: What does the data to import represent?
ASKER
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
TRUSTED BY
ASKER
Here's my dilemma: Initially I was going to use an ON DUPLICATE KEY UPDATE dynamic, but I don't think that's going to work because there's not any kind of index within the incoming data. It's only the combination of the CUSTID and the INTID that would produce a "duplicate" as opposed to a single value.
What are my options, Paul?
I need to insert a row if it's brand new or update it if it already exists...