MYSQL Compare fields and update another from same table

augustkrys
augustkrys used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst
Commented:
CREATE AFTER INSERT Trigger (T_whateverNameHere) ON Your_TABLE_NAME
  as
 set fl3 = fl1 WHERE fl1=fl2;

Author

Commented:
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?
Commented:
CREATE AFTER INSERT Trigger (T_whateverNameHere) ON dwdata.thc
  as
 set NAME` = 'MATCHED' WHERE `NPI` = `VALIDATION_NPI`;
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Author

Commented:
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,

Author

Commented:
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:

Author

Commented:
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 ;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial