Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

MySQL has something like this:

INSERT INTO visits (IP, hits)

VALUES ('127.0.0.1', 1)

ON DUPLICATE KEY UPDATE hits = hits + 1;

As far as I know, this feature doesn't exist in SQLite, what I want to know is if there is any way to achieve the same effect without having to execute two queries. Also, if this is not possible, what do you prefer:

1. SELECT + (INSERT or UPDATE) or

2. UPDATE (+ INSERT if UPDATE fails)

1 Answer

0 votes
by (40.7k points)

Try the code given below:

INSERT OR IGNORE INTO visits VALUES ($ip, 0);

UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip;

The above query requires the "ip" column to have the UNIQUE (or PRIMARY KEY) constraint.

Related questions

0 votes
1 answer
+2 votes
1 answer
0 votes
1 answer
asked Dec 12, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...