MariaDB Update Column Values Based on Another Column’s Value – How to Guide

mariadb

I have a column 'StormID' 'Basin' which both hold text based values.

StormID Basin EP012018 NEP EP022018 NEP CP012018 NEP <– I want this value to change to NCP if the first 2 characters are 'CP' in the StormID column.

Thanks for any suggestions.
Regards,
Bryan

Best Answer

You can use left() to get the first n characters of a string.

UPDATE elbat
       SET basin = 'NCP'
       WHERE left(stormid, 2) = 'CP';

Related Solutions

How to Update Row Values with SELECT in Another DB Trigger

I've modified your TRIGGER and I used ON DUPLICATE KEY UPDATE to UPDATE all the fields when your NEW.lastmodifieddate be greater than targets.lastSyncAt.

Some errors:

You've used a function on a field label in pdone.LCAPITAL(first), that can't be possible.
You've used the same function now trying to CONCAT 2 field labels, instead table fields name pdone.LCAPITAL(CONCAT(first,' ',last)).

Now, try this update of the TRIGGER:

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.account_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.id);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.id LIMIT 0,1); 
        # [ IF EXISTS IN pdone.targets, UPDATE only if NEW.lastmodifieddate IS GREATER than @TargetLastMod ]
        IF (NEW.lastmodifieddate > @TargetLastMod) THEN
            INSERT INTO pdone.targets
            (`veeva_account_id`,`veeva_timestamp`,`display_name`,`avatar_url`,`title`,`first`,`last`,`suffix`,`address1`,`address2`,`city`,`state`,`zip`,`phone`,`fax`,`target_type`,`npi`,`inactive`,`lastSyncAt`,`createdAt`,`updatedAt`)
            SELECT 
                account.id AS veeva_account_id,
                account.lastmodifieddate AS veeva_timestamp,
                pdone.LCAPITAL(CONCAT(account.firstname,' ',account.lastname)) as display_name,
                'https://pdone.s3.amazonaws.com/avatar/no_avatar.png' AS avatar_url,
                account.title__c AS `title`,
                pdone.LCAPITAL(account.firstname) AS `first`,
                pdone.LCAPITAL(account.lastname) AS `last`,
                account.suffix_vod__c AS `suffix`,
                address_vod__c.name AS `address1`,
                address_vod__c.address_line_2_vod__c AS `address2`,
                address_vod__c.city_vod__c AS `city`,
                address_vod__c.state_vod__c AS `state`,
                address_vod__c.zip_vod__c AS `zip`,
                address_vod__c.phone_vod__c AS `phone`,
                address_vod__c.fax_vod__c AS `fax`,
                -- `state_licensed_id`
                'VEEVA' AS `target_type`,
                account.npi_vod__c AS `npi`,
                FALSE AS `inactive`,
                NOW() AS `lastSyncAt`,
                NOW() AS `createdAt`,
                NOW() AS `updatedAt`
                -- address_vod__c.primary_vod__c,
                -- address_vod__c.license_vod__c,
                -- address_vod__c.lastmodifieddate AS addresslastmodifieddate
            FROM
                account
                    JOIN
                address_vod__c ON address_vod__c.account_vod__c = account.id
            WHERE
                account.id = NEW.id
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veeva_new.recordtype WHERE name = 'Professional_vod')
            ON DUPLICATE KEY UPDATE
            # `veeva_account_id` =, THIS IS TE PK, COULDNT BE UPDATE
            `veeva_timestamp` =NEW.lastmodifieddate,
            `display_name` = pdone.LCAPITAL(CONCAT(account.firstname,' ',account.lastname)),
            `avatar_url` = 'https://pdone.s3.amazonaws.com/avatar/no_avatar.png',
            `title` =account.title__c,
            `first` =pdone.LCAPITAL(account.firstname),
            `last` =pdone.LCAPITAL(account.lastname),
            `suffix` =account.suffix_vod__c,
            `address1` =address_vod__c.name,
            `address2` =address_vod__c.address_line_2_vod__c,
            `city` =address_vod__c.city_vod__c,
            `state` =address_vod__c.state_vod__c,
            `zip` =address_vod__c.zip_vod__c,
            `phone` =address_vod__c.phone_vod__c,
            `fax` =address_vod__c.fax_vod__c,
            `target_type` ='VEEVA',
            `npi` =account.npi_vod__c,
            `inactive` =FALSE,
            `lastSyncAt` = NOW(),
            `updatedAt` = NOW();
        END IF;
    ELSE
        INSERT INTO pdone.targets
            (`veeva_account_id`,`veeva_timestamp`,`display_name`,`avatar_url`,`title`,`first`,`last`,`suffix`,`address1`,`address2`,`city`,`state`,`zip`,`phone`,`fax`,`target_type`,`npi`,`inactive`,`lastSyncAt`,`createdAt`,`updatedAt`)
            SELECT 
                account.id AS veeva_account_id,
                account.lastmodifieddate AS veeva_timestamp,
                pdone.LCAPITAL(CONCAT(account.firstname,' ',account.lastname)) as display_name,
                'https://pdone.s3.amazonaws.com/avatar/no_avatar.png' AS avatar_url,
                account.title__c AS `title`,
                pdone.LCAPITAL(account.firstname) AS `first`,
                pdone.LCAPITAL(account.lastname) AS `last`,
                account.suffix_vod__c AS `suffix`,
                address_vod__c.name AS `address1`,
                address_vod__c.address_line_2_vod__c AS `address2`,
                address_vod__c.city_vod__c AS `city`,
                address_vod__c.state_vod__c AS `state`,
                address_vod__c.zip_vod__c AS `zip`,
                address_vod__c.phone_vod__c AS `phone`,
                address_vod__c.fax_vod__c AS `fax`,
                -- `state_licensed_id`
                'VEEVA' AS `target_type`,
                account.npi_vod__c AS `npi`,
                FALSE AS `inactive`,
                NOW() AS `lastSyncAt`,
                NOW() AS `createdAt`,
                NOW() AS `updatedAt`
                -- address_vod__c.primary_vod__c,
                -- address_vod__c.license_vod__c,
                -- address_vod__c.lastmodifieddate AS addresslastmodifieddate
            FROM
                account
                    JOIN
                address_vod__c ON address_vod__c.account_vod__c = account.id
            WHERE
                account.id = NEW.id
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veeva_new.recordtype WHERE name = 'Professional_vod');
    END IF;
END$$
DELIMITER ;

Hope this helps.

MySQL – Update with Subquery in MariaDB 10.1.5

As the MySQL documentation mentions at the bottom of the UPDATE documentation:

Currently, you cannot update a table and select from the same table in a subquery.

I've modified your query and I added another subquery:

update store_players as sp 
set sp.credits=sp.credits+5000 
where sp.name IN (
    select a.name from (select sp2.name from syndicate_store2.store_players as sp2,
    syndicate_ipb.nexus_purchases as np  
    left join syndicate_ipb.members as mem  
    on np.ps_member=mem.member_id  
    where sp2.authid = IntToSteam(mem.steamid)  
    and np.ps_item_id=15) AS a);

And without subqueries:

UPDATE test.store_players as sp 
JOIN syndicate_ipb.nexus_purchases AS np ON (np.ps_item_id=15)
LEFT JOIN syndicate_ipb.members as mem on (np.ps_member=mem.member_id)
SET sp.credits=sp.credits+5000 
WHERE sp.authid = IntToSteam(mem.steamid);

EDIT:

Query:

update store_players as sp 
JOIN (select sp2.name,COUNT(sp2.name) AS cnt 
        from syndicate_store2.store_players as sp2,
        syndicate_ipb.nexus_purchases as np  
        left join syndicate_ipb.members as mem  
        on np.ps_member=mem.member_id  
        where sp2.authid = IntToSteam(mem.steamid)  
        and np.ps_item_id=15
        group by sp2.name) AS q
    ON (q.name=sp.name)
set sp.credits=sp.credits+(5000*q.cnt);

PD: Replace the schema test for your store_players table schema.

Try it!

Best Answer

Related Solutions

How to Update Row Values with SELECT in Another DB Trigger

MySQL – Update with Subquery in MariaDB 10.1.5

Related Question