sqlite upsert query syntax?

user_16437633 0
Viewed 218

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

sqlite sql database
1 Answers

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;
user_104988280
Related