INSERT, UPDATE, and DELETE (I/U/D) Statements

In this Chapter:

Introduction to INSERT, UPDATE, and DELETE (I/U/D) Statements

While YQL SELECT statements allow you to read structured data from almost any source on the Web, their expressed purpose is only to read data.

To perform data manipulation, YQL provides three other SQL-like keywords for writing, updating, and deleting data mapped using a YQL Open Data Table, namely INSERT, UPDATE, and DELETE (I/U/D).

The INSERT statement inserts or adds new data to YQL tables, while the UPDATE statement updates or modifies existing data. DELETE, as the name implies, removes data.

I/U/D statements require the proper binding inputs, such as key, value, or map. The actual addition, modification, or deletion of data is performed within the Open Data Table.

Caution

Most sources that provide write capability need authentication. Examples of authentication include username/password combinations or secret API tokens.

If your table requires input that is deemed “private”, such as any passwords, authentication keys, or other “secrets”, you MUST ensure the https attribute within the tables element is set to true.

For more information on about securing private data in Open Data Tables, refer to Ensuring the Security of Private Information.

Bindings Required for I/U/D

I/U/D statements rely entirely on appropriate bindings within an Open Data Table to be usable. Specifically, you must use an insert, update, or delete bindings element. These elements help to determine what happens with the information you pass in through a YQL statement.

Consider the following INSERT statement for shortening URLs using bit.ly:

``INSERT INTO bitly.shorten (login, apiKey, longUrl) VALUES (‘USERNAME’, ‘API_KEY’, ‘http://yahoo.com‘) ``

The corresponding Open Data Table for this statement follows:

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd" https="true">
    <meta>
        <author>Nagesh Susarla</author>
        <documentationURL>http://code.google.com/p/bitly-api/wiki/ApiDocumentation</documentationURL>
    </meta>
    <bindings>
        <insert itemPath="" produces="XML">
            <urls>
                <url>http://api.bit.ly/shorten?version=2.0.1&amp;format=xml&amp;{-join|&amp;|longUrl}</url>
            </urls>
            <inputs>
                <key id="login" type="xs:string" paramType="query" required="true"/>
                <key id="apiKey" type="xs:string" paramType="query" required="true"/>
                <value id="longUrl" type="xs:string" paramType="path" required="true"/>
            </inputs>
        </insert>
     </bindings>
</table>

Run this example on the YQL console

Note

To run the example, you must replace USERNAME and API_KEY in the YQL statement with your actual bit.ly username and API key (available through your bit.ly account page).

The above Open Data Table shows one of the most basic ways to use an INSERT statement because it does not require JavaScript to massage the data. Consequently, it requires no execute `` element. It simply uses the login and apiKey as keys to authenticate the user, with the longUrl as the new value passed to the bit.ly API. For more information on how the key, value, and map values are used, refer to key/value/map elements in Using Open Data Tables.

Note

In the above example, the url element contains a URI template to aid in the construction of the URI:

{-join|&amp;|longUrl}

Here the join operator creates a key-value pair using the variable longURL along with its value. This pair is preceded by the ampersand symbol (&).

JavaScript Methods Available to I/U/D

For Web services that require specific authentication methods or specific types of HTTP requests, YQL provides several JavaScript methods for use within the execute element.

  • Methods that allow HTTP PUT, POST, and DELETE requests, in addition to GET.
  • The ability to specify the content type on data being sent, using contentType.
  • The ability to automatically convert the data being returned using accept.

For more information on the JavaScript methods available for use within I/U/D statements, refer to the JavaScript Objects, Methods, and Variables Reference.

Syntax of I/U/D

This section discusses the syntax for I/U/D statements.

Syntax of INSERT

The YQL INSERT statement has the following syntax:

INSERT INTO (table) (list of comma separated field names) VALUES (list of comma separated values)

The INSERT INTO keywords marks the start of an INSERT statement.

The table is either the YQL pre-defined or Open Data Table that represents a data source.

Following the table name is a list of field names indicating the table columns where YQL inserts a new row of data.

The VALUES clause indicates the data inserted into those columns. String values are enclosed in quotes.

In YQL, statement keywords such as SELECT and WHERE are case-insensitive. Table and field names are case sensitive. In string comparisons, the values are case sensitive. String literals must be enclosed in quotes; either double or single quotes are allowed.

Syntax of UPDATE

The YQL UPDATE statement has the following syntax:

UPDATE (table) SET field=value WHERE filter

The UPDATE keyword marks the start of an UPDATE statement. This is followed by the table name.

The table is either the YQL pre-defined or Open Data Table that represents a data source.

The SET clause is the part of the statement in which we pass new data to the update binding in the Open Data Table.

The WHERE clause indicates which data should be updated. Only remote filters can be present in the WHERE clause of an UPDATE statement.

The following example shows how the UPDATE statement syntax can look for updates to your status on Yahoo Profiles:

UPDATE social.profile.status SET status="Using YQL UPDATE" WHERE guid=me

Try this example in the YQL console

In the above example, status and guid are all bindings within the inputs element, which is nested within an update element. The status is a value element, since this is data that is updating a value using the Open Data Table. The guid binding is simply a key element, as it is a required “key” that determines ownership of this status.

Syntax of DELETE

The YQL DELETE statement has the following syntax:

DELETE FROM [table] WHERE filter

The DELETE keyword marks the start of a DELETE statement.

The table is either the YQL pre-defined or Open Data Table that represents a data source.

This is immediately followed by a remote filter that determines what table rows to remove.

The following example deletes a particular Yahoo update:

DELETE FROM social.updates WHERE guid="me" and suid="12345" and source="agg.bebo"

In the example above, the remote filters are the SUIDof the update followed by the guid of the Yahoo user and the source of the update.

Limitations of I/U/D

While I/U/D statements support most of the same functionality as the SELECT statement, there are a few caveats to keep in mind:

  • Local filtering: I/U/D statements do not support local filtering <select_filters-local>.

  • Sub-selects: INSERT statements do not support sub-selects.

  • Paging: I/U/D statements do not support paging. However, you can use sub-selects within UPDATE and DELETE statements to narrow down the values you wish to insert or delete, as shown in the following example:

    DELETE FROM table WHERE guid IN (SELECT guid FROM social.connections WHERE owner_guid = me)

Open Data Table Examples of I/U/D

The following Open Data Tables provide examples of INSERT:

Tip

To better understand the examples presented in this section, refer first to Using YQL Open Data Tables and Executing JavaScript in Open Data Tables.

Bit.ly Shorten URL (INSERT)

The following Open Data Table allows you to shorten a long URL into a bit.ly link.

This table showcases the following:

  • using INSERT statements
  • using value bindings

Example Statement:

``INSERT INTO bitly.shorten (login, apiKey, longUrl) VALUES (‘USERNAME’, ‘API_KEY’, ‘http://yahoo.com‘) ``

Open Data Table Source:

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd" https="true">
    <meta>
        <author>Nagesh Susarla</author>
        <documentationURL>http://code.google.com/p/bitly-api/wiki/ApiDocumentation</documentationURL>
    </meta>
    <bindings>
        <insert itemPath="" produces="XML">
            <urls>
                <url>http://api.bit.ly/shorten?version=2.0.1&amp;format=xml&amp;{-join|&amp;|longUrl}</url>
            </urls>
            <inputs>
                <key id="login" type="xs:string" paramType="query" required="true"/>
                <key id="apiKey" type="xs:string" paramType="query" required="true"/>
                <key id="longUrl" type="xs:string" paramType="path" required="true"/>
            </inputs>
        </insert>
     </bindings>
</table>

Run this example on the YQL console

Note

To run the example, you must replace USERNAME and API_KEY in the YQL statement with your actual bit.ly username and API key (available through your bit.ly account page).

WordPress Post (INSERT)

The following Open Data Table allows you to post to your WordPress blog. This table showcases the following:

  • calling a YQL query within execute
  • allowing INSERT in addition to SELECT statements
  • performing HTTP POST with JavaScript methods
  • creating an XML response with E4X

Example Statement:

USE "http://www.datatables.org/wordpress/wordpress.post.xml"; INSERT into wordpress.post (title, description, blogurl, username, password) values ("Test Title", "This is a test body", "http://your_wordpress_blog_site.com", "your_wordpress_username", "your_wordpress_password")

Open Data Table Source:

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
  <meta>
    <sampleQuery>insert into {table} (title, description, blogurl, username, password) values ("Test Title", "This is a test body", "http://yqltest.wordpress.com", "yqltest", "password")</sampleQuery>
  </meta>
  <bindings>
    <insert itemPath="" produces="XML">
      <urls>
        <url></url>
      </urls>
      <inputs>
        <key id='username' type='xs:string' paramType='variable' required="true" />
        <key id='password' type='xs:string' paramType='variable' required="true" />
        <key id='blogurl' type='xs:string' paramType='variable' required="true" />
        <value id='allow_comments' type='xs:string' paramType='variable' required="false" default="1" />
        <value id='tags' type='xs:string' paramType='variable' required="false"/>
        <value id='keywords' type='xs:string' paramType='variable' required="false"/>
        <value id='convert_breaks' type='xs:string' paramType='variable' required="false" default="0" />
        <value id='title' type='xs:string' paramType='variable' required="true" />
        <value id='excerpt' type='xs:string' paramType='variable' required="false"/>
        <value id='description' type='xs:string' paramType='variable' required="true"/>
        <value id='moretext' type='xs:string' paramType='variable' required="false"/>
        <value id='allow_pings' type='xs:string' paramType='variable' required="false" default= "0" />
        <value id='publish' type='xs:string' paramType='variable' required="false" default= "1" />
      </inputs>
      <execute><![CDATA[
        if (allow_comments == "true") {
          allow_comments = 1;
        } else if (allow_comments == "false") {
            allow_comments = 0;
        }
        if (convert_breaks == "true") {
          conver_tbreaks = 1;
        } else if (convert_breaks == "false") {
            convert_breaks = 0;
        }
        if (allow_pings == "true") {
          allow_pings = 1;
        } else if (allow_pings == "false") {
          allow_pings = 0;
        }
        postData =
        <methodCall><methodName>metaWeblog.newPost</methodName>
          <params>
            <param>
              <value>
                <string>1</string>
              </value>
            </param>
            <param>
              <value>
                <string>{username}</string>
              </value>
            </param>
            <param>
              <value>
                <string>{password}</string>
              </value>
            </param>
            <param>
              <value>
                <struct>
                  <member>
                    <name>mt_allow_comments</name>
                    <value>
                      <int>{allow_comments}</int>
                    </value>
                  </member>
                  <member>
                    <name>mt_convert_breaks</name>
                      <value>
                        <string>{convert_breaks}</string>
                      </value>
                  </member>
                  <member>
                    <name>title</name>
                    <value>
                      <string>{title}</string>
                    </value>
                  </member>
                  <member>
                    <name>description</name>
                    <value>
                      <string>{description}</string>
                    </value>
                  </member>
                  <member>
                    <name>mt_allow_pings</name>
                    <value>
                      <string>{allow_pings}</string>
                    </value>
                  </member>
                </struct>
              </value>
            </param>
            <param>
              <value>
                <boolean>{publish}</boolean>
              </value>
            </param>
          </params>
        </methodCall>;
        if (tags) {
          postData.params.param..struct.member +=
          <member>
            <name>mt_tags</name>
            <value>
              <string>{tags}</string>
            </value>
          </member>;
        } else {
            postData.params.param..struct.member +=
            <member>
              <name>mt_tags</name>
                <value>
                  <string></string>
                </value>
            </member>;
        }
        if (moretext) {
          postData.params.param..struct.member +=                      <member>
            <name>mt_text_more</name>
            <value>
              <string>{moretext}</string>
            </value>
          </member>;
        } else {
            postData.params.param..struct.member +=
            <member>
              <name>mt_text_more</name>
              <value>
                <string></string>
              </value>
            </member>;
        }
        if (excerpt) {
          postData.params.param..struct.member +=
          <member>
            <name>mt_excerpt</name>
            <value>
              <string>{excerpt}</string>
            </value>
          </member>;
        } else {
          postData.params.param..struct.member +=
          <member>
            <name>mt_excerpt</name>
            <value>
              <string></string>
            </value>
          </member>;
        }
        if (keywords) {
          postData.params.param..struct.member +=
          <member>
            <name>mt_keywords</name>
            <value>
              <string>{keywords}</string>
            </value>
          </member>;
        } else {
          postData.params.param..struct.member +=
          <member>
            <name>mt_keywords</name>
            <value>
              <string></string>
            </value>
          </member>;
        }
        url = blogurl + "/xmlrpc.php";
        myRequest = y.rest(url);
        myRequest.contentType("text/xml");
        //myRequest.accept("text/xml");
        myRequest.header("Connection", "close");
        results = myRequest.post('<?xml version="1.0" encoding="UTF-8"?>' + postData.toString()).response;
        postId = results.params.param.value.string.toString();
        //response.object = results;
        response.object = <postid>{postId}</postid>;
      ]]>
    </execute>
  </insert>
  <update itemPath="" produces="XML">
    <urls>
      <url></url>
    </urls>
    <inputs>
      <key id='username' type='xs:string' paramType='variable' required="true" />
      <key id='password' type='xs:string' paramType='variable' required="true" />
      <key id='blogurl' type='xs:string' paramType='variable' required="true" />
      <key id='postid' type='xs:string' paramType='variable' required="true" />
      <value id='allow_comments' type='xs:string' paramType='variable' required="true" />
      <value id='tags' type='xs:string' paramType='variable' required="true"/>
      <value id='keywords' type='xs:string' paramType='variable' required="true"/>
      <value id='convert_breaks' type='xs:string' paramType='variable' required="true" />
      <value id='title' type='xs:string' paramType='variable' required="true" />
      <value id='excerpt' type='xs:string' paramType='variable' required="true"/>
      <value id='description' type='xs:string' paramType='variable' required="true"/>
      <value id='moretext' type='xs:string' paramType='variable' required="true"/>
      <value id='allow_pings' type='xs:string' paramType='variable' required="true" />
      <value id='publish' type='xs:string' paramType='variable' required="true"/>
    </inputs>
    <execute>
      <![CDATA[
        if (allow_comments == "true") {
          allow_comments = 1;
        } else if (allow_comments == "false") {
            allow_comments = 0;
        }
        if (convert_breaks == "true") {
          conver_tbreaks = 1;
        } else if (convert_breaks == "false") {
            convert_breaks = 0;
        }
        if (allow_pings == "true") {
          allow_pings = 1;
        } else if (allow_pings == "false") {
            allow_pings = 0;
        }
        postData = <methodCall>
        <methodName>metaWeblog.newPost</methodName>
        <params>
          <param>
            <value>
              <string>1</string>
            </value>
          </param>
          <param>
            <value>
              <string>{username}</string>
            </value>
          </param>
          <param>
            <value>
              <string>{password}</string>
            </value>
          </param>
          <param>
            <value>
              <struct>
                <member>
                  <name>mt_allow_comments</name>                               <value>
                    <int>{allow_comments}</int>
                  </value>
                </member>
                <member>
                  <name>mt_convert_breaks</name>                               <value>
                    <string>{convert_breaks}</string>
                  </value>
                </member>
                <member>
                  <name>title</name>
                  <value>
                    <string>{title}</string>
                  </value>
                </member>
                <member>
                  <name>description</name>
                  <value>
                    <string>{description}</string>
                  </value>
                </member>
                <member>
                  <name>mt_allow_pings</name>
                  <value>
                    <string>{allow_pings}</string>                             </value>
                </member>
              </struct>
            </value>
          </param>
          <param>
            <value>
              <boolean>{publish}</boolean>
            </value>
          </param>
        </params>
      </methodCall>;
      if (tags) {
        postData.params.param..struct.member +=
        <member>
          <name>mt_tags</name>
          <value>
            <string>{tags}</string>
          </value>
        </member>;
      } else {
        postData.params.param..struct.member +=
        <member>
          <name>mt_tags</name>
          <value>
            <string></string>
          </value></member>;
      }
      if (moretext) {
        postData.params.param..struct.member +=
        <member>
          <name>mt_text_more</name>
          <value>
            <string>{moretext}</string>
          </value>
        </member>;
      } else {
          postData.params.param..struct.member +=
          <member>
            <name>mt_text_more</name>
            <value>
              <string></string>
            </value>
          </member>;
        }
        if (excerpt) {
          postData.params.param..struct.member +=
          <member>
            <name>mt_excerpt</name>
            <value>
              <string>{excerpt}</string>
            </value>
          </member>;
        } else {
            postData.params.param..struct.member +=                      <member>
              <name>mt_excerpt</name>
              <value>
                <string></string>
              </value>
            </member>;
        }
        if (keywords) {
          postData.params.param..struct.member +=
          <member>
            <name>mt_keywords</name>
            <value>
              <string>{keywords}</string>
            </value>
          </member>;
        } else {
            postData.params.param..struct.member +=
            <member>
              <name>mt_keywords</name>
              <value>
                <string></string>
              </value>
            </member>;
        }
      ]]>
    </execute>
  </update>
  <select itemPath="" produces="XML">
    <urls>
      <url></url>
    </urls>
    <inputs>
      <key id='postid' type='xs:string' paramType='variable' required="true" />
      <key id='blogurl' type='xs:string' paramType='variable' required="true" />
      <key id='username' type='xs:string' paramType='variable' required="true" />
      <key id='password' type='xs:string' paramType='variable' required="true" />
    </inputs>
    <execute><![CDATA[
      postData = <methodCall>
      <methodName>metaWeblog.getPost</methodName>                    <params>
          <param>
            <value>
              <string>{postid}</string>
            </value>
          </param>
          <param>
            <value>
              <string>{username}</string>
            </value>
          </param>
          <param>
            <value>
              <string>{password}</string>
            </value>
          </param>
        </params>
      </methodCall>;
      url = blogurl + "/xmlrpc.php";
      myRequest = y.rest(url);
      myRequest.contentType("text/xml");
      //myRequest.accept("text/xml");
      myRequest.header("Connection", "close");
      results = myRequest.post('<?xml version="1.0" encoding="UTF-8"?>' + postData.toString()).response;
      response.object = results;
    ]]></execute>
    </select>
  </bindings>
</table>

Run this example in the YQL Console. Be sure to use your blog’s URL, username, and password as values for the blogurl, username, and password keys.