![]() UPDATE tbl_cash_sales_batches SET control_totals = (SELECT amount FROM cash_sales_sums WHERE cash_sales_sums.batch_key = tbl_cash_sales_batches.batch_key LIMIT 1),Įxpected_number_of_documents = (SELECT no_of_docs FROM cash_sales_sums WHERE cash_sales_sums.batch_key = tbl_cash_sales_batches. )A ON A.cash_sale_document_id = tbl_cash_sales_documents.cash_sale_id Tbl_cash_sales_transactions.cash_sale_document_id Tbl_cash_sales_transactions.cash_sale_document_id, SUM(tbl_cash_sales_documents.paid_amount) paid_amount, SUM(A.amount - tbl_cash_sales_documents.bonus_amount - tbl_cash_sales_documents.discount_given) amount, SELECT tbl_cash_sales_documents.batch_key, COUNT(DISTINCT tbl_cash_sales_documents.cash_sale_number) no_of_docs, DROP TABLE IF EXISTS cash_sales_sums ĬREATE TEMPORARY TABLE cash_sales_sums as The best thing is create temporary table from the query, then use the temporary table to update as one query. If you are updating from a complex query. I found that that was too complex to incorporate into a SELECT within mysql, so I created a temporary table, and joined that with the update statement:- CREATE TEMPORARY TABLE activity_product_ids AS () ON sku=CONCAT('L',SUBSTR(product_type_code,3), 'U',unit_index, 'A',activity_index) Query: CREATE DATABASE geeks Step 2: Use database Use the below SQL statement to switch the database context to geeks: Query: USE geeks Step 3: Table definition We have two tables named ‘demotable1’ and ‘demotable2’ in our geek’s database. ![]() JOIN product_types USING(product_type_id) Step 1: Create a database we can use the following command to create a database called geeks. I identified the following to get a list of activity_id vs product_id:- SELECT a.activity_id, w.product_id ![]() Those SKUs are then stored in a different table. I needed to populate the product_id field in the activities table, where activities are numbered in a unit, and units are numbered in a level (identified using a string ?N), such that one can identify activities using an SKU ie L1U1A1. This is an alternative solution, to a more complex version of the problem, which I thought might be useful. I found this question in looking for my own solution to a very complex join. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |