Avatar of augustkrys
augustkrys
 asked on

MYSQL Compare fields and update another from same table

I have a single mySql table. i have 2 fields fl1 and fl2. If their values equal then update fl3 with a static value like matched.

I need to tigger this on new entries only
MySQL ServerSQL

Avatar of undefined
Last Comment
augustkrys

8/22/2022 - Mon
SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
augustkrys

ASKER
Here is the actual update I want to turn into a trigger
This functions correctly

UPDATE dwdata.thc t
 SET t.`NAME` = 'MATCHED'

WHERE t.`NPI` =t.`VALIDATION_NPI`

So what is the proper syntax for the trigger here?
ASKER CERTIFIED SOLUTION
Dan Craciun

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
augustkrys

ASKER
I am running this trigger inside workbench and this is what I get
ERROR 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 'AFTER INSERT Trigger (T_whateverNameHere) ON dwdata.thc
  as
 set `NAME` = 'MAT' at line 1
SQL Statement:
CREATE AFTER INSERT Trigger (T_whateverNameHere) ON dwdata.thc
  as
 set `NAME` = 'MATCHED' WHERE `NPI` = `VALIDATION_NPI`;

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'thc' already exists
SQL Statement:
CREATE TABLE `thc` (
  `DWDOCID` int(11) NOT NULL default '0',
  `DWPAGECOUNT` int(11) default NULL,
  `DWDISKNO` int(11) default NULL,
  `DWFLAGS` int(11) default NULL,
  `DWOFFSET` int(11) default NULL,
  `DWSTOREDATETIME` timestamp NULL default NULL,
  `DWSTOREUSER` varchar(20) default NULL,
  `DWMODDATETIME` timestamp NULL default NULL,
  `DWMODUSER` varchar(20) default NULL,
  `DWLASTACCESSDATETIME` timestamp NULL default NULL,
  `DWLASTACCESSUSER` varchar(20) default NULL,
  `DWSTORAGEACCESS` longtext,
  `DWEXTENSION` varchar(20) default NULL,
  `DWHEADERSIZE` bigint(20) default NULL,
  `DWDOCSIZE` bigint(20) default NULL,
  `DWVERID` int(11) NOT NULL,
  `DWSYSVERSION` int(11) NOT NULL default '0',
  `DWFLAGSEX` bigint(20) default NULL,
  `DWINTELLIXHINTS` longtext,
  `DOCUMENT_TYPE` varchar(50) default NULL,
  `NAME` varchar(50) default NULL,
  `DATE` datetime default NULL,
  `ID` varchar(50) default NULL,
  `DWUSRVERSION` char(10) default NULL,
  `DWVERSTATUS` char(15) default NULL,
  `DWVERCOMMENT` char(255) default NULL,
  `NPI` varchar(50) default NULL,
  `VALIDATION_NPI` varchar(50) default NULL,
  `STATUS` varchar(50) default NULL,
augustkrys

ASKER
This is the Trigger I have tried-
USE `dwdata`;

DELIMITER $$

DROP TRIGGER IF EXISTS dwdata.thc_AUPD$$
USE `dwdata`$$
CREATE TRIGGER `MYthc_AUPD` AFTER UPDATE ON `thc` FOR EACH ROW
begin
      IF NPI = VALIDATION_NPI then
      UPDATE STATUS = 'MATCHED' (also tried Set STATUS = 'MATCHED' and instead of status being seen as a field it gets seen as a variable?)
      
END IF;
END;
$$
DELIMITER ;

THis is what I get when i execute

ERROR 1193: Unknown system variable 'STATUS'
SQL Statement:
CREATE TRIGGER `MYthc_AUPD` AFTER UPDATE ON `thc` FOR EACH ROW
begin
      IF NPI = VALIDATION_NPI then
      SET STATUS = 'MATCHED'
      else STATUS = 'REVIEW'
END IF;
END;

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'thc' already exists-(not sure why i get this - of course it exists thats the point trying to update)
SQL Statement:
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
augustkrys

ASKER
Okay - Here is my Stored prodecure that works that I would like to make a trigger or schedule and event. My biggest challenge here is that I am working with MySQL 5.0.84. The stored procedure works and runs in Query Browser as workbench doesnt support my version

DELIMITER $$

DROP PROCEDURE IF EXISTS `checkme` $$
CREATE DEFINER=`dwadmin`@`localhost` PROCEDURE `checkme`()
BEGIN
UPDATE dwdata.thc t
 SET t.`STATUS` = 'MATCHED'

WHERE t.`NPI` =t.`VALIDATION_NPI`;
END $$

DELIMITER ;