• Registered: 2020-05-22
  • Posts: 31

Topic: If exist update table

Greetings,
I need sql query to check if value exist or not
I got two tables

table1:
id                   name                 exist_in_table2
1                    john                   yes
2                    dave                  no
3                    mark                  no


table2:
id              id_table1
1                     1
2                     6
3                     7



I want to update table1 column "exist_in_table2" to yes if exists or no if not exists

BR

  • Registered: 2014-06-25
  • Posts: 874

Re: If exist update table

How about this?: https://www.sqlitetutorial.net/sqlite-exists/

  • Registered: 2020-05-22
  • Posts: 31

Re: If exist update table

UPDATE table1
SET  exist_in_table2 =
    CASE WHEN EXISTS (SELECT id FROM table2 WHERE table2.id_table1=table1.id)
            THEN "yes"
            ELSE "no"
        END     


if someone need

4 Reply by derek (edited by derek 2021-08-03 09:38:28)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,814

Re: If exist update table

Hi Argonx, Tcoton,
Another option is to use a calculated field (no need to update table1).:
.
(case
when (select count(*) from table2 where table2.id_table1 = table1.id) = 0 then 'No'
else 'Yes'
end)   
.
Derek.