CODEWITHSUNDEEP
sqlite
Jean-Baptiste Bolh
Jean-Baptiste Bolh

Reputation: 27

Modify column records based on values in a different table

I am trying to modify records in a column of one table whenever I meet some condition in another table.

Specifically, whenever there is a 'y' in the RobotComRein.Reinforcement table/column, I want to add a 1 to the table/column: RobotComYN.Yesses.

Furthermore, both tables have columns called "RobotID" and "Command" and I would like to make the change only when the following condition is met:

RobotComRein.RobotID = RobotComYN.RobotID AND RobotComRein.Command = RobotComYN.Command

I have tried messing around with the following SQL statement.

UPDATE RobotComYN
SET Yesses = Yesses + 1
WHERE RobotComRein.Reinforcement IN (
    SELECT RobotComRein.Reinforcement
    FROM RobotComRein
    WHERE RobotComRein.Reinforcement = 'y' 
    AND RobotComRein.RobotID = RobotComYN.RobotID 
    AND RobotComRein.Command = RobotComYN.Command
);

I receive the following error message: "no such column: RobotComRein.Reinforcement"

Upvotes: 0

Views: 44

Answers (1)

C Perkins
C Perkins

Reputation: 3884

It's all about context and the order that tables are referenced. You are trying to reference another table from the UPDATE's WHERE clause, but there is no relationship between the two tables at that level. It is okay to reference the "outer" table from the nested query inside the parenthesis, but not vice versa. In other words, you are updating RobotComYN, so you can't directly reference RobotComRein table columns in the WHERE clause.

See How to UPDATE a SQLITE Column with an INNER JOIN on TWO FIELDS. The following are customized statements based on the accepted answer:

UPDATE RobotComYN
SET Yesses = Yesses + 1
WHERE EXISTS (SELECT RobotComRein.RobotID 
              FROM RobotComRein
              WHERE (RobotComRein.RobotID = RobotComYN.RobotID)
                AND (RobotComRein.Command = RobotComYN.Command)
                AND RobotComRein.Reinforcement = 'y'
             );

Notice that the inner select of the original answer was SELECT *, but it is unnecessary to return all columns just to test for existence of a matching row. Only a single column is needed and is more efficient.

UPDATE RobotComYN
SET Yesses = Yesses + 1
WHERE (RobotID, Command) IN (
    SELECT RobotComRein.RobotID, RobotComRein.Command 
    FROM RobotComRein
    WHERE RobotComRein.Reinforcement = 'y');

The following update sets the new value from an aggregate query that counts the rows of the related table:

UPDATE RobotComYN
SET Yesses = Yesses + (
    SELECT Count(RobotComRein.RobotID) AS increase
    FROM RobotComRein
    WHERE RobotComRein.RobotID = RobotComYN.RobotID
          AND RobotComRein.Command = RobotComYN.Command
          AND RobotComRein.Reinforcement = 'y')
WHERE (RobotID, Command) IN (
    SELECT RobotComRein.RobotID, RobotComRein.Command 
    FROM RobotComRein
    WHERE RobotComRein.Reinforcement = 'y');

I was originally thinking that a WITH statement would eliminate the need for multiple subqueries, but that flawed thinking eventually gets back to the fact that the sqlite UPDATE statement does not support direct joins on the updated table. The WITH statement still does not eliminate the need to redefine the "join" columns for both sub-queries--it does not simplify the query. About the only benefit to the WITH statement is that the WITH query will be executed only once which applies any filters and aggregates.

WITH filtered AS (
     SELECT RobotID, Command, Count(*) AS increase
        FROM RobotComRein
        WHERE RobotComRein.Reinforcement = 'y'
        GROUP BY RobotID, Command)
UPDATE RobotComYN
SET Yesses = Yesses + (
    SELECT increase
    FROM filtered
    WHERE filtered.RobotID = RobotComYN.RobotID
          AND filtered.Command = RobotComYN.Command)
WHERE (RobotID, Command) IN (
    SELECT filtered.RobotID, filtered.Command 
    FROM filtered);

Upvotes: 1

Related Questions