2

table register

key app
123 insta

table inbox

EventSourceApplication EventName MessageId(primarykey) MessageText status
facebook appointmentbooked CAR20124 SUCCESSFULL ok

I want to upsert into table inbox only if there is a specific data in table register in this case key = 123 & app = insta

my query is like

INSERT INTO inbox(EventSourceApplication,EventName,MessageId ,MessageText,status)
VALUES ('fb1','appointmentbooked','CAR201246','SUCCESSFULL','nope') ON CONFLICT(MessageId) 
DO UPDATE SET EventSourceApplication = EXCLUDED.EventSourceApplication,
EventName = EXCLUDED.EventName,MessageText=EXCLUDED.MessageText
WHERE (SELECT 1 FROM register WHERE key='123' AND app = 'insta')

Is the query wrong ? since i am able to insert into inbox even if wrong data is given

jeena
  • 33
  • 4
  • Does this answer your question? [sqlite insert into table select \* from](https://stackoverflow.com/questions/16838896/sqlite-insert-into-table-select-from) – astentx Jul 13 '21 at 08:14
  • i am not moving data between tables i am only checking if data is present in table register then only upsert table insert where columns in both table are interdependent – jeena Jul 13 '21 at 08:17

1 Answers1

0

The WHERE clause that you need is valid only in a SELECT statement, so instead of VALUES use SELECT and use EXISTS properly:

INSERT INTO inbox(EventSourceApplication, EventName, MessageId, MessageText, status)
SELECT 'fb1', 'appointmentbooked', 'CAR201246', 'SUCCESSFULL', 'nope' 
WHERE EXISTS (SELECT 1 FROM register WHERE key='123' AND app = 'insta') 
ON CONFLICT(MessageId) DO UPDATE SET 
  EventSourceApplication = EXCLUDED.EventSourceApplication,
  EventName = EXCLUDED.EventName,
  MessageText=EXCLUDED.MessageText;
forpas
  • 160,666
  • 10
  • 38
  • 76