Oracle PL/SQL - Inserting values based on max of columns
pl/sql block to insert data
oracle select max value from multiple rows
oracle sql max
pl sql max of two values
insert in loop oracle
select statement in pl/sql block
oracle sql examples
I am trying to insert data (product, timestamp) into a table xyz from tables A, B & C with a primary key as PRODUCT. All the three table have a timestamp column along with other columns. I want to insert row with product and max of timestamp of these three tables (A, B & C).
You don't specify what you want to happen if product already exists in table xyz, so on the assumption that it doesn't, you could use a query like this:
Insert into XYZ(Product, Timestamp) Select Product, max(timestamp) from (select product, timestamp from a union all select product, timestamp from b union all select product, timestamp from c) group by product
insert, For you to insert rows into the base table of a view, the owner of the schema is a host variable or PL/SQL variable that stores the retrieved expr value. For each How to retrieve the column name which has maximum value by comparing the values from multiple columns using 'Case' statements 0 Case statement in select oracle compare multiple columns
You might try the following:
INSERT INTO xyz ( product, timestamp ) SELECT MAX(product) KEEP ( DENSE_RANK FIRST ORDER BY timestamp DESC ) AS product , MAX(timestamp) FROM a UNION ALL SELECT MAX(product) KEEP ( DENSE_RANK FIRST ORDER BY timestamp DESC ) AS product , MAX(timestamp) FROM b UNION ALL SELECT MAX(product) KEEP ( DENSE_RANK FIRST ORDER BY timestamp DESC ) AS product , MAX(timestamp) FROM c;
Hope this helps.
6 Performing SQL Operations from PL/SQL, In a cursor FOR loop, PL/SQL For each column value returned by the the MAX(), MIN(), or AVG() value for a You can create a table or insert into a In PL/SQL, we can insert the data into any table using the SQL command INSERT INTO. This command will take the table name, table column and column values as the input and insert the value in the base table. The INSERT command can also take the values directly from another table using 'SELECT' statement rather than giving the values for each column.
UPDATED as per Sentine's comment:
Try this (syntax not verified):
INSERT INTO xyz (product , timestamp) ( SELECT product , MAX(A.timestamp) FROM A UNION SELECT product , MAX(B.timestamp) FROM B UNION SELECT product , MAX(C.timestamp) FROM C ) ;
Using Min/Max in where conditions - Ask Tom, SQL> select * from gak ; no rows selected. SQL> Insert into gak (CURR, POS, LCYPOS, DEALNO, DDATE, CTIMESTAMP, CONPOS) Values ('AED', 100, 1000, Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.
Oracle / PLSQL: MAX Function, The Oracle / PLSQL MAX function returns the maximum value of an expression. Because you have listed one column in your SELECT statement that is not Adding user_name to both the select list and to the group by clause returns Assigns the values of expressions to corresponding columns in the column list. If there is no column list, the first value is inserted into the first column defined by the CREATE TABLE statement, the second value is inserted into the second column, and so on. There must be one value for each column in the column list.
Oracle / PLSQL: INSERT Statement, The Oracle INSERT statement is used to insert a single record or multiple column2, column_n: The columns in the table to insert values. expression1, Developers and DBAs get help from Oracle experts on: Dynamically vary the number of columns in a query
The Ultimate Guide to Oracle INSERT ALL Statement, In this statement, each value expression val1, val2, or val3 must refer to a column returned by the select list of the subquery. If you want to use literal values instead Triggers can derive column values automatically, based upon a value provided by an INSERT or UPDATE statement. This type of trigger is useful to force values in specific columns that depend on the values of other columns in the same row. BEFORE row triggers are necessary to complete this type of operation for these reasons:
- You should show what you have tried so far, why it does not work.. don't throw your question out and expect people to make the query for you
- Could you please add more details like an example of desired result ; Source table data examples
- He wants to get MAX timestamp of all tables, meaning 3 records with the max of each table (A, B and C).
- No The OP stated that Product is Primary Key of table XYZ therefore only one Product record, and Max timestamp across all three tables, A, B, and C. Please remove your -1.
- You can't insert 4 columns from the subquery into just two columns in the destination table like that.
- @Sentinel, rRe-thinking