Repository: kayak/pypika
Pull request #147 (2018-08-08 11:33:08)

Docs and fix for insert/update+join queries
Added docs for insert from and update queries and added examples in the docs


Patch:
diff --git a/README.rst b/README.rst
index cfaa461..d01df1d 100644
--- a/README.rst
+++ b/README.rst
@@ -606,8 +606,8 @@ Insert with on Duplicate Key Update
 wrapper to update to the value specified in the ``INSERT`` clause.
 
 
-Insert with a SELECT Query
-""""""""""""""""""""""""""
+Insert from a SELECT Sub-query
+""""""""""""""""""""""""""""""
 
 .. code-block:: sql
 
@@ -640,6 +640,34 @@ builder chain.
 
     INSERT INTO customers_backup SELECT * FROM customers
 
+.. code-block:: python
+
+    customers, customers_backup = Tables('customers', 'customers_backup')
+
+    q = Query.into(customers_backup).columns('id', 'fname', 'lname')
+        .from_(customers).select(customers.id, customers.fname, customers.lname)
+
+.. code-block:: sql
+
+    INSERT INTO customers_backup SELECT "id", "fname", "lname" FROM customers
+
+The syntax for joining tables is the same as when selecting data
+
+.. code-block:: python
+
+    customers, orders, orders_backup = Tables('customers', 'orders', 'orders_backup')
+
+    q = Query.into(orders_backup).columns('id', 'address', 'customer_fname', 'customer_lname')
+        .from_(customers)
+        .join(orders).on(orders.customer_id == customers.id)
+        .select(orders.id, customers.fname, customers.lname)
+
+.. code-block:: sql
+
+   INSERT INTO "orders_backup" ("id","address","customer_fname","customer_lname")
+   SELECT "orders"."id","customers"."fname","customers"."lname" FROM "customers"
+   JOIN "orders" ON "orders"."customer_id"="customers"."id"
+
 Updating Data
 ^^^^^^^^^^^^^^
 PyPika allows update queries to be constructed with or without where clauses.
@@ -648,9 +676,9 @@ PyPika allows update queries to be constructed with or without where clauses.
 
     customers = Table('customers')
 
-    Query.update(customers).set('last_login', '2017-01-01 10:00:00')
+    Query.update(customers).set(customers.last_login, '2017-01-01 10:00:00')
 
-    Query.update(customers).set('lname', 'smith').where(customers.id == 10)
+    Query.update(customers).set(customers.lname, smith).where(customers.id == 10)
 
 .. code-block:: sql
 
@@ -658,6 +686,22 @@ PyPika allows update queries to be constructed with or without where clauses.
 
     UPDATE "customers" SET "lname"='smith' WHERE "id"=10
 
+The syntax for joining tables is the same as when selecting data
+
+.. code-block:: python
+
+    customers, profiles = Tables('customers', 'profiles')
+
+    Query.update(customers)
+         .join(profiles).on(profiles.customer_id == customers.id)
+         .set(customers.lname, profiles.lname)
+
+.. code-block:: sql
+
+   UPDATE "customers"
+   JOIN "profiles" ON "profiles"."customer_id"="customers"."id"
+   SET "customers"."lname"="profiles"."lname"
+
 .. _tutorial_end:
 
 
diff --git a/docs/2_tutorial.rst b/docs/2_tutorial.rst
index 22752bf..6bff198 100644
--- a/docs/2_tutorial.rst
+++ b/docs/2_tutorial.rst
@@ -6,5 +6,5 @@ Tutorial
    :end-before:  _tutorial_end:
 
 .. include:: ../README.rst
-    :start-after: _appendix_start:
-    :end-before:  _appendix_end:
+   :start-after: _appendix_start:
+   :end-before:  _appendix_end:
diff --git a/pypika/queries.py b/pypika/queries.py
index 5cf9ef1..b468ca7 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -664,14 +664,21 @@ class QueryBuilder(Selectable, Term):
 
         if self._update_table:
             querystring = self._update_sql(**kwargs)
+
+            if self._joins:
+                querystring += " " + " ".join(join.get_sql(**kwargs)
+                                              for join in self._joins)
+
             querystring += self._set_sql(**kwargs)
 
             if self._wheres:
                 querystring += self._where_sql(**kwargs)
 
             return querystring
-        elif self._delete_from:
+
+        if self._delete_from:
             querystring = self._delete_sql(**kwargs)
+
         elif not self._select_into and self._insert_table:
             querystring = self._insert_sql(**kwargs)
 
@@ -683,6 +690,7 @@ class QueryBuilder(Selectable, Term):
                 return querystring
             else:
                 querystring += ' ' + self._select_sql(**kwargs)
+
         else:
             querystring = self._select_sql(**kwargs)
 


Test patch:
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index 92235ce..281e5e6 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -507,4 +507,8 @@ class UpdateQueryJoinTests(unittest.TestCase):
             .where(b.foo == 1) \
             .set('adwords_batch_job_id', 1)
 
-        self.assertEqual('UPDATE "a" SET "adwords_batch_job_id"=1 WHERE "b"."foo"=1', str(q))
+        self.assertEqual('UPDATE "a" '
+                         'JOIN "b" '
+                         'ON "a"."fkey_id"="b"."id" '
+                         'SET "adwords_batch_job_id"=1 '
+                         'WHERE "b"."foo"=1', str(q))