SQLite-UPSERT*नहीं*INSERT या स्थानांतरित करें




(12)

http://en.wikipedia.org/wiki/Upsert

SQL सर्वर पर संग्रहीत प्रोसेस अद्यतन डालें

क्या SQLite में ऐसा करने का कोई चालाक तरीका है जिसे मैंने नहीं सोचा है?

मूल रूप से मैं रिकॉर्ड में मौजूद होने पर चार में से तीन कॉलम अपडेट करना चाहता हूं, यदि यह अस्तित्व में नहीं है तो मैं चौथे कॉलम के लिए डिफ़ॉल्ट (एनयूएल) मान के साथ रिकॉर्ड दर्ज करना चाहता हूं।

आईडी एक प्राथमिक कुंजी है इसलिए यूपीएसईआरटी के लिए केवल एक रिकॉर्ड होगा।

(मैं यह निर्धारित करने के लिए चयन के ऊपरी हिस्से से बचने की कोशिश कर रहा हूं कि मुझे अद्यतन करने या स्पष्ट रूप से INSERT की आवश्यकता है)

सुझाव?

मैं तालिका की रचना के लिए SQLite साइट पर Syntax की पुष्टि नहीं कर सकता। मैंने इसका परीक्षण करने के लिए डेमो नहीं बनाया है, लेकिन ऐसा लगता है कि यह समर्थित नहीं है ..

अगर ऐसा होता, तो मेरे पास तीन कॉलम होते हैं, इसलिए यह वास्तव में ऐसा दिखाई देगा:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

लेकिन पहले दो ब्लब्स एक संघर्ष नहीं करेंगे, केवल आईडी होगा तो मैं ब्लॉब 1 और ब्लॉब 2 को प्रतिस्थापित नहीं किया जाएगा (वांछित के रूप में)

डेटा बाध्यकारी करते समय SQLite में अद्यतन एक पूर्ण लेनदेन है, जिसका अर्थ है कि प्रत्येक भेजी गई पंक्ति को अद्यतन करने के लिए आवश्यक है: INSERT के विपरीत बयान तैयार / चरण / अंतिम विवरण जो रीसेट फ़ंक्शन के उपयोग की अनुमति देता है

एक कथन वस्तु का जीवन इस तरह कुछ जाता है:

  1. Sqlite3_prepare_v2 () का उपयोग कर ऑब्जेक्ट बनाएं
  2. Sqlite3_bind_ इंटरफेस का उपयोग कर पैरामीटर होस्ट करने के लिए मानों को बाध्य करें।
  3. Sqlite3_step () को कॉल करके SQL चलाएं
  4. Sqlite3_reset () का उपयोग करके कथन को रीसेट करें, फिर चरण 2 पर वापस जाएं और दोहराएं।
  5. Sqlite3_finalize () का उपयोग कर कथन ऑब्जेक्ट को नष्ट करें।

अपडेट मैं अनुमान लगा रहा हूं आईएनएसईआरटी की तुलना में धीमी है, लेकिन यह प्राथमिक कुंजी का उपयोग करके SELECT से तुलना कैसे करता है?

शायद मुझे चौथे कॉलम (ब्लोब 3) को पढ़ने के लिए चयन का उपयोग करना चाहिए और फिर पहले 4 कॉलम के लिए नए डेटा के साथ मूल 4 वें कॉलम को मिलाकर एक नया रिकॉर्ड लिखने के लिए REPLACE का उपयोग करना चाहिए?


मुझे एहसास है कि यह एक पुराना धागा है लेकिन मैं देर से sqlite3 में काम कर रहा हूं और इस विधि के साथ आया हूं जो गतिशील रूप से पैरामीटरयुक्त क्वेरी उत्पन्न करने की मेरी आवश्यकताओं को बेहतर बनाता है:

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

यह अभी भी 2 प्रश्न हैं जहां अपडेट पर क्लॉज है लेकिन यह चाल चल रहा है। मेरे पास यह भी मेरे दृष्टिकोण में है कि एसकलाइट अपडेट स्टेटमेंट को पूरी तरह अनुकूलित कर सकता है अगर कॉल करने के लिए कॉल () शून्य से अधिक है। चाहे वह वास्तव में करता है या नहीं, यह मेरे ज्ञान से परे है, लेकिन एक आदमी सपना देख सकता है? ;)

बोनस प्वाइंट्स के लिए आप इस लाइन को जोड़ सकते हैं जो आपको पंक्ति की आईडी देता है चाहे वह एक नई डाली गई पंक्ति या मौजूदा पंक्ति हो।

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

share पर विस्तार से आप एक डमी 'सिंगलटन' तालिका (एक ही पंक्ति के साथ अपनी खुद की सृजन की एक तालिका) से चयन कर सकते हैं। यह कुछ नकल से बचाता है।

मैंने MySQL और SQLite में पोर्टेबल उदाहरण भी रखा है और एक उदाहरण के रूप में एक 'date_added' कॉलम का उपयोग किया है कि आप केवल पहली बार कॉलम कैसे सेट कर सकते हैं।

 REPLACE INTO page (
   id,
   name,
   title,
   content,
   author,
   date_added)
 SELECT
   old.id,
   "about",
   "About this site",
   old.content,
   42,
   IFNULL(old.date_added,"21/05/2013")
 FROM singleton
 LEFT JOIN page AS old ON old.name = "about";

एरिस्टोटल पागाल्टज़िस और एरिक बी के उत्तर से कोलेसेज के विचार के बाद, यहां केवल कुछ कॉलम अपडेट करने या पूर्ण पंक्ति डालने का एक अपरिवर्तनीय विकल्प है यदि यह अस्तित्व में नहीं है।

इस मामले में, कल्पना करें कि शीर्षक और सामग्री को अपडेट किया जाना चाहिए, जब मौजूदा नहीं हो तो अन्य पुराने मानों को रखें और नाम न मिलने पर आपूर्ति किए गए हों:

नोट id को इंसर्ट होने पर मजबूर होना पड़ता है क्योंकि इसे ऑटोइनक्रिकमेंट माना जाता है। यदि यह केवल एक जेनरेट की गई प्राथमिक कुंजी है तो COALESCE का भी उपयोग किया जा सकता है ( अरिस्टोटल पागाल्टज़िस टिप्पणी देखें)।

WITH new (id, name, title, content, author)
     AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) )
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT
     old.id, COALESCE(old.name, new.name),
     new.title, new.content,
     COALESCE(old.author, new.author)
FROM new LEFT JOIN page AS old ON new.name = old.name;

तो सामान्य नियम होगा, यदि आप पुराने मान रखना चाहते हैं, तो COALESCE उपयोग करें, जब आप मानों को अपडेट करना चाहते हैं, तो new.fieldname उपयोग new.fieldname


यदि आप आमतौर पर अपडेट कर रहे हैं तो मैं ..

  1. एक लेनदेन शुरू करो
  2. अद्यतन करो
  3. पंक्ति गणना की जांच करें
  4. यदि यह 0 है तो सम्मिलित करें
  5. कमिट

यदि आप आम तौर पर आवेषण कर रहे होते हैं तो मैं करता हूं

  1. एक लेनदेन शुरू करो
  2. एक डालने का प्रयास करें
  3. प्राथमिक कुंजी उल्लंघन त्रुटि के लिए जाँच करें
  4. अगर हमें कोई त्रुटि मिलती है तो अपडेट करें
  5. कमिट

इस तरह आप चयन से बचें और आप स्क्लाइट पर लेनदेन से ध्वनि कर रहे हैं।


यहां एक समाधान है जो वास्तव में एक इंसर्ट या प्रतिस्थापन के बजाय एक यूपीएसईआरटी (अद्यतन या INSERT) है (जो कई परिस्थितियों में अलग-अलग काम करता है)।

यह इस तरह काम करता है:
1. यदि एक ही आईडी वाला रिकॉर्ड मौजूद है तो अपडेट करने का प्रयास करें।
2. यदि अद्यतन ने किसी भी पंक्ति को नहीं बदला है ( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0) , तो रिकॉर्ड डालें।

तो या तो एक मौजूदा रिकॉर्ड अद्यतन किया गया था या एक डालने का प्रदर्शन किया जाएगा।

परिवर्तन विवरण () एसक्यूएल फ़ंक्शन का उपयोग यह जांचने के लिए है कि अद्यतन कथन किसी भी मौजूदा रिकॉर्ड को हिट करता है या केवल अगर कोई रिकॉर्ड नहीं मारा गया तो केवल सम्मिलित कथन निष्पादित करता है।

उल्लेख करने की एक बात यह है कि परिवर्तन () फ़ंक्शन निम्न-स्तरीय ट्रिगर्स द्वारा किए गए परिवर्तनों को वापस नहीं करता है ( http://sqlite.org/lang_corefunc.html#changes ), इसलिए इसे ध्यान में रखना सुनिश्चित करें।

यहां एसक्यूएल है ...

परीक्षण अद्यतन:

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 2, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

टेस्ट डालने:

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 3, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

सम्मिलित या स्थान "यूपीएसईआरटी" के बराबर नहीं है

मान लें कि मेरे पास फ़ील्ड आईडी, नाम और भूमिका के साथ कर्मचारी है:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

बूम, आपने कर्मचारी संख्या 1 का नाम खो दिया है। SQLite ने इसे डिफ़ॉल्ट मान के साथ बदल दिया है।

यूपीएसईआरटी की अपेक्षित आउटपुट भूमिका को बदलने और नाम रखने के लिए होगी।


इस धागे को पढ़ने के बाद और निराश हो गया कि यह "यूपीएसईआरटी" आईएनजी के लिए आसान नहीं था, मैंने आगे की जांच की ...

आप वास्तव में SQLITE में इसे सीधे और आसानी से कर सकते हैं।

उपयोग करने के बजाय: INSERT INTO

उपयोग करें: अंदर INSERT OR REPLACE INTO

यह वही करता है जो आप करना चाहते हैं!


यह विधि इस प्रश्न के उत्तर में कुछ अन्य विधियों को रीमिक्स करती है और सीटीई (सामान्य तालिका अभिव्यक्तियों) के उपयोग को शामिल करती है। मैं क्वेरी पेश करूँगा और समझाऊंगा कि मैंने ऐसा क्यों किया जो मैंने किया था।

यदि कोई कर्मचारी 300 है तो मैं कर्मचारी 300 से डेविस के लिए अंतिम नाम बदलना चाहता हूं। अन्यथा, मैं एक नया कर्मचारी जोड़ूंगा।

तालिका का नाम: कर्मचारी कॉलम: आईडी, first_name, last_name

क्वेरी है:

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
    SELECT --this is needed to ensure that the null row comes second
        *
    FROM (
        SELECT --an existing row
            *
        FROM
            employees
        WHERE
            employee_id = '300'

        UNION

        SELECT --a dummy row if the original cannot be found
            NULL AS employee_id,
            NULL AS first_name,
            NULL AS last_name
    )
    ORDER BY
        employee_id IS NULL --we want nulls to be last
    LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
    registered_employees.employee_id, --if this is null the SQLite default will be used
    COALESCE(registered_employees.first_name, 'SALLY'),
    'DAVIS'
FROM
    registered_employees
;

असल में, मैंने डिफ़ॉल्ट मान निर्धारित करने के लिए चयनित कथन का उपयोग करने की संख्या को कम करने के लिए सीटीई का उपयोग किया था। चूंकि यह एक सीटीई है, इसलिए हम केवल तालिका से इच्छित कॉलम का चयन करते हैं और INSERT कथन इसका उपयोग करता है।

अब आप तय कर सकते हैं कि आप कोल्स के फ़ंक्शन में, मूल्यों के साथ क्या होना चाहिए, नल को प्रतिस्थापित करके आप किस डिफ़ॉल्ट का उपयोग करना चाहते हैं।


तालिका में 3 कॉलम मानते हैं .. आईडी, NAME, ROLE

बीएडी: यह आईडी = 1 के लिए नए मानों के साथ सभी स्तंभों को सम्मिलित या प्रतिस्थापित करेगा:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

बीएडी: यह कॉलम के 2 को सम्मिलित या प्रतिस्थापित करेगा ... NAME कॉलम को पूर्ण या डिफ़ॉल्ट मान पर सेट किया जाएगा:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

अच्छा: यह 2 कॉलम अपडेट करेगा। जब आईडी = 1 मौजूद है, तो NAME अप्रभावित होगा। जब आईडी = 1 मौजूद नहीं है, तो नाम डिफ़ॉल्ट (NULL) होगा।

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

यह कॉलम के 2 अपडेट करेगा। जब आईडी = 1 मौजूद है, तो ROLE अप्रभावित होगा। जब आईडी = 1 मौजूद नहीं है, तो भूमिका डिफ़ॉल्ट मान के बजाय 'बेंचवार्मर' पर सेट की जाएगी।

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

SQLite के लिए INAALID हो सकता है (कम से कम एंड्रॉइड और WebSQL में काम नहीं कर रहा)

मुझे पता है कि मैं पार्टी के लिए देर हो चुकी हूं लेकिन ....

UPDATE employee SET role = 'code_monkey', name='fred' WHERE id = 1;
INSERT INTO employee(id, role, name) values (1, 'code monkey, 'fred') WHERE changes() = 0;

तो यह अद्यतन करने का प्रयास करता है, अगर रिकॉर्ड वहां है तो बदलता है () == 1 इसलिए सम्मिलन एक्शन-एड नहीं है।

वैकल्पिक रूप से:

ऐसा करने का एक और पूरी तरह से अलग तरीका यह है: मेरे आवेदन में मैंने अपनी स्मृति पंक्ति में लंबे समय तक सेट किया। मैक्सवेल्यू जब मैं स्मृति में पंक्ति बना देता हूं। (MaxValue कभी भी एक आईडी के रूप में उपयोग नहीं किया जाएगा जो आप लंबे समय तक नहीं जीएंगे .... फिर यदि पंक्ति आईडी वह मान नहीं है तो यह पहले से ही डेटाबेस में होना चाहिए, इसलिए यदि मैक्सवेल्यू है तो उसे अद्यतन की आवश्यकता है, तो उसे एक डालने की आवश्यकता है। यह केवल तभी उपयोगी होता है जब आप अपने ऐप में पंक्ति आईडी ट्रैक कर सकें।


मुझे पता है कि सबसे अच्छा तरीका एक अद्यतन करने के बाद, एक डालने के बाद है। "चयन का ओवरहेड" जरूरी है, लेकिन यह एक भयानक बोझ नहीं है क्योंकि आप प्राथमिक कुंजी पर खोज कर रहे हैं, जो तेज़ है।

आप जो भी चाहते हैं उसे करने के लिए आपको अपनी तालिका और फ़ील्ड नामों के साथ नीचे दिए गए बयानों को संशोधित करने में सक्षम होना चाहिए।

--first, update any matches
UPDATE DESTINATION_TABLE DT
SET
  MY_FIELD1 = (
              SELECT MY_FIELD1
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
 ,MY_FIELD2 = (
              SELECT MY_FIELD2
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
WHERE EXISTS(
            SELECT ST2.PRIMARY_KEY
            FROM
              SOURCE_TABLE ST2
             ,DESTINATION_TABLE DT2
            WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY
            );

--second, insert any non-matches
INSERT INTO DESTINATION_TABLE(
  MY_FIELD1
 ,MY_FIELD2
)
SELECT
  ST.MY_FIELD1
 ,NULL AS MY_FIELD2  --insert NULL into this field
FROM
  SOURCE_TABLE ST
WHERE NOT EXISTS(
                SELECT DT2.PRIMARY_KEY
                FROM DESTINATION_TABLE DT2
                WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY
                );

मुझे लगता है कि यह वही हो सकता है जो आप खोज रहे हैं: कॉन्फ्लिक्ट क्लॉज पर

यदि आप अपनी तालिका को इस तरह परिभाषित करते हैं:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    field1 TEXT 
); 

अब, यदि आप किसी आईडी के साथ INSERT करते हैं जो पहले से मौजूद है, तो SQLite स्वचालित रूप से INSERT के बजाय अद्यतन करता है।

HTH ...





upsert