Ryan Castle Ryan Castle - 6 months ago 11
MySQL Question

MySQL - Updating a table IF record doesn't exist in another table

So, I'm writing a multi-query for my program that checks if a user has viewed a page or not. If they haven't viewed it, a record is created in a table named

views
, and then a record named
numViews
is updated in a table named
pages
, whereas if they've already viewed it, nothing happens.

I have the code written for the insertion of the record to
views
, which keeps all records unique. The problem I am facing is when it comes to updating a record in
pages
.

In Pseudo terms, the following is what I wish to be achieved:

if (record_exits_in_table_named_views == false) {
UPDATE record IN pages
INSERT record INTO views
}


The following is my query for adding a record:

INSERT INTO views
(`pageID`, `viewerID`)
SELECT
T.*
FROM
(SELECT
'.$this->pageID.' pageID,
'.$_SESSION['userID'].' viewerID
)
T LEFT JOIN
views
ON
views.creationID = T.pageID
AND
views.viewerID = T.viewerID
WHERE
views.viewerID IS NULL;


All help is appreciated.
Thanks

Answer

for the update if i understand right ..should be this (not in clause)

 update pages
 set numViews  =numViews +1
 where (pageID, viewID) not in (select pageID, viewID from views)