![]() Inserts new services from the api table into the service table and the second: UPDATE service s SET active = false The first CTE INSERT INTO service (name, id, active) WHERE s.name NOT IN (SELECT name FROM api) RETURNING s.name WHERE a.name NOT IN (SELECT name FROM service) RETURNING name So now, because of PostgreSQL's ability to perform INSERTs and UPDATEs within CTEs, we can now do the following: WITH cte1 (nom) AS Notice that service Test1 is missing and that service Test4 is an additional service. I've assumed that your API doesn't know the status of the service, so it only has two fields - the name and the name and the id. Now, you receive your data from your API - I'll assume that you put that into some sort of temporary table - the keyword TEMPORARY just means that the table will be dropped at the end of your session - I've tested with both TEMPORARY and normal tables with the fiddle and the results are the same, so we'll go with TEMPORARY: CREATE TEMPORARY TABLE api Populate it with your data: INSERT INTO service VALUES It's based on Common Table Expressions ( CTEs) and that fact that with PostgreSQL, you can perform not only SELECTs, but also INSERTs, UPDATEs and DELETEs (see here also).įirst, your service table: CREATE TABLE service I think you will want something like the following - see the fiddle here. ![]() So if the new list does not have Test3 the existing Test3 row would be updated to show Active as false.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |