MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE user (
  `ID` INTEGER,
  `firstname` VARCHAR(5),
  `lastname` VARCHAR(10),
  `address` VARCHAR(26),
  `count` INTEGER
);

INSERT INTO user
  (`ID`, `firstname`, `lastname`, `address`)
VALUES
  ('1', 'john', 'doe', 'james street, idaho, usa'),
  ('2', 'cindy', 'smith', 'rollingwood av,lyn, canada'),
  ('3', 'rita', 'chatsworth', 'arajo ct, alameda, cali'),
  ('4', 'randy', 'plies', 'smith spring, lima, peru'),
  ('5', 'Matt', 'gwalio', 'park lane, atlanta, usa');

CREATE TABLE address_effect (
  `address` VARCHAR(7),
  `effect` VARCHAR(13)
);

INSERT INTO address_effect
  (`address`, `effect`)
VALUES
  ('idaho', 'potato, tater'),
  ('canada', 'cold, tundra'),
  ('fremont', 'crowded'),
  ('peru', 'alpaca'),
  ('atlanta', 'peach, cnn'),
  ('usa', 'big, hard');

Records: 5  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0


UPDATE user u
SET u.count = (
  SELECT COUNT(*)
  FROM address_effect a
  WHERE FIND_IN_SET(a.address, REPLACE(u.address, ', ', ','))
)

Rows matched: 5  Changed: 5  Warnings: 0


SELECT * FROM user


ID
firstname
lastname
address
count


1
john
doe
james street, idaho, usa
2

2
cindy
smith
rollingwood av,lyn, canada
1

3
rita
chatsworth
arajo ct, alameda, cali
0

4
randy
plies
smith spring, lima, peru
1

5
Matt
gwalio
park lane, atlanta, usa
2

ID	firstname	lastname	address	count
1	john	doe	james street, idaho, usa	2
2	cindy	smith	rollingwood av,lyn, canada	1
3	rita	chatsworth	arajo ct, alameda, cali	0
4	randy	plies	smith spring, lima, peru	1
5	Matt	gwalio	park lane, atlanta, usa	2