CODEWITHSUNDEEP
sqlpostgresql
VericalId
VericalId

Reputation: 105

How to update a table based on the inner joined field that it has with another table?

I have two tables (calendar and friend) that have the same id field. in this case a dateid. In the calendar table the ids are related to an actual date such as '02-02-2019'.Friends table has times I met a certain friend the dateid and his id. I wanna update the times we have met a certain day but I can't seem to be able to update only him and for one day in specific I update all friends for all dates.The queries I have tried so far are as follows:

Update Friends Set timesmet=timesmet+1
From Friends as t1 inner join calendar as t2
on t1.dateid=t2.dateid
where t2.dateofday='02-02-2019' and t1.friendid=1

where dateofday is the date as '02-02-2019'. I seem to update all friends and all the dates. Can someone suggest a way to do this correctly? or do I have to store the dateid that I wanna update by calling a select first from the calendar?

Also I tried:

UPDATE Friends
INNER JOIN calendar
ON Friends.dateid = calendar.dateid
SET Friends.timesmet = timesmet+1
WHERE Friends.friendid=1 and calendar.dateofday='02-02-2019'.

I'm using postgres and this last one tells me that there is a syntax error in the inner join which is probably because update doe snot have this syntax but all were suggested online in several post I found here in this platform

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

In Postgres, the syntax is:

update Friends f
    Set timesmet = timesmet + 1
from calendar c
where f.dateid = c.dateid and
      c.dateofday = '02-02-2019' and
      f.friendid = 1;

Upvotes: 1

Related Questions