CODEWITHSUNDEEP
sqlsqlite
CptNemo
CptNemo

Reputation: 6755

Create table based on COUNT condition

I have two tables in my database. The table up_to_date indicates for each record the number of comments (commentNumber) that a specific page (url) should contain. The table comment contains the actual comments I have in the db with the corresponding url. 

CREATE TABLE up_to_date
 (id INTEGER PRIMARY KEY,
 url TEXT NOT NULL,
 commentNumber INTEGER)

CREATE TABLE comment 
 (id INTEGER PRIMARY KEY,
 commentMessage TEXT,
 url TEXT NOT NULL)

I want to create a table url_to_update with the url of pages which I need to update: the number of records in comment for a specific page is smaller than what indicated in up_to_date.commentNumber for the same page.

Something like

CREATE TABLE url_to_update AS
 (SELECT * FROM up_to_date 
WHERE up_to_date.commentNumber > COUNT(comment.url = up_to_date.url))

Upvotes: 0

Views: 53

Answers (1)

Hamidreza
Hamidreza

Reputation: 3128

in SQLite you can create a table with this syntax create table Table_2 as select * from Table_1:

CREATE TABLE url_to_update AS
SELECT * FROM up_to_date 
WHERE up_to_date.commentNumber > (SELECT COUNT(comment.url) FROM comment 
WHERE comment.url= up_to_date.url);

Here is a sample which you can see

Upvotes: 2

Related Questions