CODEWITHSUNDEEP
sqldatabasesqlitetriggers
Reacting Innocent
Reacting Innocent

Reputation: 37

Increase the value of a specific row depending on the input of another table using triggers SQlite

My problem is this : Whenever I insert a word into a table named A, table B must update it's value by +1 in the row with ID same as the number of letters in the input word. This must be done with triggers. For example if I input the word ("macaroni") on table A, table's B column named value with ID (8) must increase by 1.

For example

ID-value

8 - 1

CREATE TRIGGER update_value
after insert on A
for each ROW
BEGIN
 SELECT id FROM B LIMIT CHAR_LENGTH(A),1; 
 update B set value = value + 1;
end

It, of course, does not work a I am pretty new to SQLite so I would much appreciate your help on how to solve this!

EDIT: Table A contains one column(words) while Table B contains the two columns, the id and value. So we probably have to enter the value of the id every time we have a word length that wasn't previously added.

Upvotes: 1

Views: 71

Answers (2)

forpas
forpas

Reputation: 164089

I assume that the column ID in the table B is the primary key.
If the table B does not contain a row for each possible length, you can use UPSERT inside the trigger, so that if a word with a new length is inserted in A then a new row with value = 1 will be inserted in B, else the existing row will be incremented:

CREATE TRIGGER update_value AFTER INSERT ON A
BEGIN
  INSERT INTO B(id, value)
  SELECT LENGTH(NEW.word), 1
  ON CONFLICT(id) DO UPDATE
  SET value = value + 1;
END

See the demo.

Upvotes: 1

Shawn
Shawn

Reputation: 52344

Assuming table definitions like

create table wordlens(id integer primary key, count integer);
create table words(id integer primary key, word text);

Something like this trigger:

create trigger update_value after insert on words
begin
  insert or ignore into wordlens values (length(new.word), 0);
  update wordlens set count = count + 1 where id = length(new.word);
end;

First it adds a new row for the given length if it doesn't already exist in the length table, and then it increases the appropriate row's count by 1.

Example usage:

sqlite> insert into words(word) values ('macaroni');
sqlite> select * from wordlens;
id  count
--  -----
8   1
sqlite> insert into words(word) values ('abcdefgh');
sqlite> select * from wordlens;
id  count
--  -----
8   2

Upvotes: 1

Related Questions