how to update one column with trigger mysql



  • i have 2 tables the first one is

    Table KEYS

    game keys
    WOW AAAAAAA-AAAAAAA-AAAAA
    WOW BBBBBBB-BBBBBBB-BBBBB
    L4D2 AAAAAAA-AAAAAAA-AAAAA

    TABLE inventory

    game country language pieces
    WOW USA EN 0
    L4D2 USA EN 0

    What i want to achieve is that column pieces on inventory gets updated by COUNT the game column of the keys table so at the end my inventory table should look like

    game country language pieces
    WOW USA EN 2
    L4D2 USA EN 1

    What i have tried

    DELIMITER $$
    CREATE TRIGGER 'inventario' AFTER INSERT ON `KEYS` FOR EACH ROW BEGIN
    UPDATE inventory.pieces WITH SELECT COUNT(*) keys.game FROM keys WHERE keys.game = inventory.game
    END $$;
    

    i dont know if theres any other method to achieve this automatically after updating/inserting data on KEYS table



  • If you are saying that the row in inventory necessarily exists, the body of the Trigger is something like this:

    FOR EACH ROW
        UPDATE inventory
            SET pieces = pieces + 1
            WHERE old.game = inventory.game
    

    If you need to insert rows that have not yet been created:

    FOR EACH ROW
        INSERT INTO inventory (
            ( game, country, language, pieces )
            VALUES
            ( OLD.game, 'USA', 'EN', 1 )
        ON DUPLICATE KEY UPDATE
            SET pieces = pieces + 1
    

    (Something would need to be done if it is not USA and EN.)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2