Link to home
Start Free TrialLog in
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
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of augustkrys
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,
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:
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 ;