Update the SQL column based on filter of another column?
sql update column with value from another column in the same table
sql update column with value from another table
update column from another table sql server
update one column from another column in same table oracle
update one column from another column in different table oracle
update statement with variable in sql server
update table from another table mysql
I am stuck in a SQL query. I want to update the column STATUS using sql query. The exact requirement is I have a SQL table as follows.
_____________________________________________ start_date end_date status --------------------------------------------- 2014/01/03 | 2018/01/03 | FAILED 2016/12/15 | 2017/01/03 | SUCCESS 2014/01/03 | 2010/01/13 | FAILED 2017/10/23 | 2017/01/03 | FAILED 2014/01/03 | 2011/01/03 | FAILED 2017/11/13 | 2017/01/03 | SUCCESS ---------------------------------------------
In the above table, I have start_date, end_date and status. Now I want to update the status from "FAILED" to "SUSPEND" where the end_date is the oldest one among all the dates and start_date is same but end_date is different. (Here only one row will be updated as showing in the below table)
I am trying to build a query but unable to fetch the required result. after applying a correct query, the table should look like as follows.
start_date end_date status --------------------------------------------- 2014/01/03 | 2018/01/03 | FAILED 2016/12/15 | 2017/01/03 | SUCCESS 2014/01/03 | 2010/01/13 | SUSPEND 2017/10/23 | 2017/01/03 | FAILED 2014/01/03 | 2011/01/03 | FAILED 2017/11/13 | 2017/01/03 | SUCCESS ---------------------------------------------
Can anyone please help me in this. Thanks in Advance.
In a Derived Table, get minimum value of end_date for a group of start_date . Join it your main table based on start_date and min_end_date . Update Query. UPDATE Table t1 INNER JOIN Temp t2 ON t1.code1 = t2.code1 AND t1.code2 = t2.code2 SET t1.Col = t2.Mins; Alternatively, try with domain aggregates like DMax(), DMin(), DCount(), etc. Below assumes code fields are number types:
You could use an update using a where clause. I didn't understand what you mean with and start_date is same but end_date is different, so i didn use it.
UPDATE table1 SET status='SUSPEND' WHERE status='FAILED' and end_date<start_date
The SQL UPDATE statement is used to modify column values within a SQL Server table. Notice we used the primary key SalesPersonID to filter the row. going to show you how to update's column values based on a match to another table. A while ago I was visiting a customer that asked if they can filter a query data by a column from another query in Power BI. And I said of course you can. In this post I explain how that can be achieved in Power Query. The key point is to know how to reference … Continue reading Quick Tips: How to Filter a Column by another Column from a Different Query in Power Query
Whatever the logic Madhur told is correct except a functional bug. Since you were asking for oldest.. We should query using MIN instead of MAX as below
UPDATE your_table AS t1 JOIN (SELECT start_date, MIN(end_date) AS max_end_date FROM your_table GROUP BY start_date) AS t2 ON t2.start_date = t1.start_date AND t2.max_end_date = t1.end_date SET t1.status = 'SUSPEND' WHERE t1.status = 'FAILED'
This clause makes it possible to filter only those rows you wish to include in An equality condition tests that a column or expression matches a value. I remember that <> stands for not equals by seeing that the < and > oppose each other. I am trying to write a measure which changes the column used for calculation based on filter selection. Example: If the selected filter value is A, measure should calculate using column 1, if selected filter value is B, then the measure should calculate using column 2, if nothing is selected, it should calulate using column 3.
This SQL Server tutorial explains how to use the UPDATE statement in SQL Server There are 3 syntaxes for the UPDATE statement depending on whether you are performing a traditional update or updating one table with data from another table. UPDATE table1 SET table1.column = table2.expression1 FROM table1 Hello All, I would like to change a value after the table updates \ inserts a new row based on the value of that column. Table Name is: MPanel. Col Names: RID, FPID.
It then uses the column names following the SELECT keyword to determine This processing order means that we can filter records using WHERE based on values in columns We can use many other Boolean operators to filter our data. The filter used is the year column. I need to calculate Hiring Rate as HR = count of hires/((COunt of employees on 12/31/2016 + count of employees on 12/31/2017))/2 if the year chosen is 2017. Similarly, HR in 2018 eould be count of hires/((Count if employees on 12/31/2017 + Count of employees on 7/31/2017))/2 if year is 2018.
One way is to add a new conditional column and with the following logic: So we only need to modify the Power Query code as below: replace Hello, I have this situation: TABLE2 Line Expense CodArea Date Office 1 10 Area1 01/01/2017 Office1 2 20 Area3 02/01/2017 Office1 3 15 Area2 03/02/2017 Office1 4 15 Area2 04/01/2017 Office1 5 20 Area3 05/01/2017 Office3 6 15 Area1 06/03/2017 Office2 7 30 Area3 07/01/2017 Office3 8 25 Area
- Is there any PK in this table ? This looks like a bad design. What is the purpose of this table ?
- "the end_date is the oldest one and start_date is same but end_date is different." what's the meaning of this sentence? start_date > end_date?
- No there is no PK in the table. this is a sample table actual table has the huge amount of data and many columns.
- @ZhongWang it means there are multiple end date but I want to update only that STATUS where end date is oldest among all dates.
- Hi, I edited my question for better understanding.
- hey madhur it is giving error. Error at Command Line:52 Column:15 Error report: SQL Error: ORA-00971: missing SET keyword 00971. 00000 - "missing SET keyword"
- @JayeshVyas are you using MySQL or Oracle Database (PL/SQL) ? It looks like you are definitely not using MySQL
- I am using Sqldeveloper not PL/SQL
- @JayeshVyas why did you tag this question as MySQL ? My Solution works for MySQL. Oracle , MySQL, SQL Server etc are all different RDBMS and have different syntax at times. Anyways, I am editing answer to add solution for Oracle also.
- @JayeshVyas please join me in chat here: chat.stackoverflow.com/rooms/181674/…
- thanks for ur quick response but I want to update only that particular row where status is FAILED and end date is the oldest one (plus I have to identified that how many rows has the same start dates.)