ORACLE - Update a value from two different columns in differente tables with a filter

oracle update multiple columns from another table
oracle update multiple columns from same table
sql update multiple columns from another table
oracle sql update multiple columns
sql update column with value from another table
update column from another table oracle
update column based on another column sql
sql update column with value from another column in the same table

I'have a little question about a query. I have to update a column from a table where there are only record of expense(integer). I must increase the expense of 5% if the client is from a specific state, the column of the state is in a different table and the key in common is the address.

This is my query below :

UPDATE table 1 a
SET expense_vl = (
SELECT expense*1.05 FROM table 1
LEFT JOIN table2 b  ON b.ADDRESS_ID=a.ADDRESS_ID
WHERE description_state IN 'lollyland'
)

Try below

UPDATE table1 a SET expense_vl = (SELECT expense*1.05
                                  FROM table2 b
                                  WHERE b.ADDRESS_ID=a.ADDRESS_ID)
WHERE description_state IN 'lollyland'

Ask TOM "Update table with multiple columns from another , How to update multiple columns in single SQL ? update around 70 columns and some columns may have null values such as Purchase supplier etc ) working on filters any outgoing "code" thru email or it will be stopped. To update a column using a sum of values from other tables that may have nulls, do something like this: UPDATE Holiday_Reservation R SET Subtotal = NVL( (SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.In_Flight_ID), 0) + NVL( (SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.Out_Flight_ID), 0) + NVL( (SELECT AC.Acc_PricePn FROM Accommodation AC WHERE AC.Acc_ID = R.Acc_ID), 0);

Or try with subselect:

UPDATE table1
   SET expense_vl =  expense*1.05
 WHERE ADDRESS_ID IN (SELECT ADDRESS_ID FROM table2 WHERE description_state IN 'lollyland')

Tutorial: Querying and Filtering Rows: Databases , So to get all the rows and visible columns in the toys table, use: select * from For example, the following two queries search for the same values from each column. Well the database processes the filters in different orders. UPDATE product SET price = price / 2 WHERE category_id IN ( SELECT category_id FROM product GROUP BY category_id HAVING COUNT (*) = 1 ); The subquery finds all the category_id values where COUNT is 1. We don’t need to have COUNT in the SELECT part of the subquery, however, if we do, the query will display an error.

I think you need to change your query like below :

 UPDATE table 1 A
    SET expense_vl=expense*1.05 FROM table 1
    LEFT JOIN table2 B ON B.ADDRESS_ID=A.ADDRESS_ID
    WHERE B.description_state IN 'lollyland'

Tutorial: Update & Transactions: Databases for , This tutorial teaches you how to change values using the update Filtering Updates. It's rare you want to update all the rows in a table. Usually Even though the two updates set different columns, the second is still blocked. You need to create a third table (projects) with unique values them make a relationship between this table and the other two then use this as your slicer to filter your charts. Be aware that you need to use this column to use as axisor values on your visuals.

I'd recommend using a semi-join:

update table_1 a
set expense_v1 = expense * 1.05
where exists (
  select null
  from table2 b
  where
    a.address_id = b.address_id and
    b.description_state = 'lollyland'
)

Althought I must add that it would help if you include the DDL for your table. We're sort of guessing at which table "description" came from.

Also, when possible, include sample input for each table and desired output. We don't need a million records, just an example that illustrates your issue.

The UNION [ALL], INTERSECT, MINUS Operators, SQL Statements: SAVEPOINT to UPDATE You can combine multiple queries using the set operators UNION , UNION ALL , INTERSECT , and MINUS . If the queries select values of CHAR with different lengths, then the returned value (​using the TO_CHAR function) when columns do not exist in one or the other table​: This approach is based on your "compare tables" technique from asktom and Oracle Magazine. When the same PK is present in both tables, I present the data in two rows instead of one. The COL_DIF_ID column mimicks the GROUPING_ID Function: it is a bit vector that indicates which columns actually changed.

merge, You cannot update the same row of the target table multiple times in the same specifies values to insert into the column of the target table if the condition of the ON To insert all of the source rows into the table, you can use a constant filter  If you want to select columns from different tables with a priority, you can: - (full) outer join all the tables - Use coalesce to return the first non-null column from these tables in the desired order Plugging this into your data gives something like:

Selecting and Filtering Rows, Table 7-6 Using Multiple FILTER Statements FILTER clause to ensure that the value of the source COUNT column before an update matches the value in the  The first syntactical form, called a searched update, updates the value of one or more columns for all rows of the table for which the WHERE clause evaluates to TRUE. The second syntactical form, called a positioned update, updates one or more columns on the current row of an open, updatable cursor.

How to compare two tables of data - Ask Tom, Then use a MERGE statement to do update when the hash value is different, or insert where Bytes=25376) 2 1 UNION-ALL 3 2 FILTER 4 3 INDEX (FAST FULL SCAN) OF I need to compare two large tables with 30 columns on each table. I have two tables, 1 with the monthly inventory report and one (dashboard) with PO data. The relationship that I have created between those two tables is One to manywith crossfilter direction: both. Now, what I am trying to calculate in a new calculated column is the sum of inventory (from the monthly report) / sum of PO Qty

Comments
  • Thank you really!