logo
down
shadow

updating multiple columns using case statement in sql server


updating multiple columns using case statement in sql server

By : user2956238
Date : November 22 2020, 10:54 AM
around this issue You'll have to swap the syntax around. The case statement will be applied for every value you want to update...
code :
UPDATE table SET
    pay1 = CASE WHEN @columnname IN('name1') THEN pay1 * 100 ELSE pay1 END,
    pay2 = CASE WHEN @columnname IN('name1', 'name2') THEN pay2 * 20 ELSE pay2 END,
    pay3 = CASE WHEN @columnname IN('name1', 'name2', 'name3') THEN pay3 * 100 ELSE pay3 END
IF @columnname = 'name1'
    UPDATE table SET pay1 = pay1 * 100, pay2=pay2*20, pay3=pay3* 100

ELSE IF @ColumnName = 'name2'
    UPDATE table SET pay2 = pay2 * 20, pay3 = pay3 * 100

ELSE IF @ColumnName = 'name3'
    UPDATE table SET pay3 = pay3 * 100


Share : facebook icon twitter icon
When updating multiple columns, prevent updating null in case of no row is returned from select

When updating multiple columns, prevent updating null in case of no row is returned from select


By : jkdoug
Date : March 29 2020, 07:55 AM
This might help you I think the following will work (I don't have DB2 on hand to test it):
code :
UPDATE TABLE1 A
    SET (A.COL1, A.COL2) =
        (SELECT COALESCE(MAX(B.COL1), A.COL1),
                COALESCE(MAX(B.COL2), A.COL2)
         FROM TABLE2 B
         WHERE A.KEY = B.KEY
        );
Creating stored procedure for updating columns by case statement

Creating stored procedure for updating columns by case statement


By : Adriano Neres
Date : March 29 2020, 07:55 AM
wish help you to fix your issue You need to make sure that everything is nvarchar type when concatenating parts to form your dynamic query else you would get errors. Therefore, you should be using the following code rather than what you have right now.
Note that all parameter values are being cast to nvarchar data type in code below.
code :
SET @DynamicPivotQuery = 

        'UPDATE [Table1] SET [StrataID] = '+ cast(@NewValue as nvarchar(100)) +'
         WHERE[ID] = ''a'' and [FormID] = ''b'' 
           AND ItemID ='+ cast( @ColumnName as nvarchar(500)) +';'
DECLARE @ItemValues TABLE (
 SequenceNumber int IDENTITY (1, 1),
 ItemID int
)
INSERT INTO @ItemValues (ItemID)
SELECT
 ItemId
FROM Table1
WHERE [ID] = 'a'
AND [FormID] = 'b';

--populate the corresponding values for StrataID in same sequence as
--ItemId values in above table
DECLARE @StrataIDValues TABLE (
  SequenceNumber int IDENTITY (1, 1),
  StrataID int
)
INSERT INTO @StrataIDValues (StrataID)
VALUES (1), (2), (5);

UPDATE [Table1]
 SET [StrataID] = v.StrataID
 FROM Table1 t
  INNER JOIN (SELECT
               ItemID,
               StrataID
            FROM @ItemValues
    INNER JOIN @StrataIDValues
 ON [@ItemValues].SequenceNumber = [@StrataIDValues].SequenceNumber) v
 ON t.ItemID = v.ItemID;
R Case statement using both and or in the same statement - between Dates, some with missing values over multiple columns

R Case statement using both and or in the same statement - between Dates, some with missing values over multiple columns


By : Chris Martinus
Date : March 29 2020, 07:55 AM
To fix the issue you can do I'm trying to see whether or not a program participant was active on the beginning of each month. , Using dynamic variable creation of dplyr:
code :
library(dplyr) # version 0.6 and above
library(lubridate)

df <- df %>% 
  select(Admission, Discharge) %>% 
  mutate_all(mdy) # convert the columns to date format

# Start dates of the months and respective month names
my_months <- ymd("2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01")
month_names <- month(my_months, label=T) %>% as.character()


# Looping through the months and dynamic creation of month columns
for (i in seq(length(my_months))){
  df <- df %>%  
    mutate(!!month_names[i] := ifelse(my_months[i] > Admission &
                                        (my_months[i] < Discharge | is.na(Discharge)), 
                                        "Active", "NotActive"))
}
Update multiple columns using same SQL server case statement

Update multiple columns using same SQL server case statement


By : LooPFlooD
Date : March 29 2020, 07:55 AM
this one helps.
Is it possible to update both columns using a single case statement ?
updating multiple columns of a sql server row in a single statement

updating multiple columns of a sql server row in a single statement


By : Tathagata Kundu
Date : March 29 2020, 07:55 AM
will be helpful for those in need For the sake of making things nice and clear for the next guy who comes along to work on it.....
Related Posts Related Posts :
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • Is it possible to maximum occurences of a value for each key in my table all in one query?
  • Divide table into groups of 5000 based on rank
  • rolling back exec and sp_OA methods
  • How to convert multiple rows to one row in SQL Server?
  • How to use substring and charindex in google query language
  • Find table information where part of column matches char variable
  • Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = colu
  • Assigning an index to a constraint
  • Retrieve records from multiple Records returned by Sub-Query
  • SQL DELETE group of records based on opposite group being empty
  • SQL GetDate() returns wrong time
  • Total sales amount for customers in cities
  • SQL Server -- updating the `sys.*` tables and not just reading them
  • SQL Order By using concat
  • SQL server 2008 - subquery returned more than one value
  • Group Data Hourly and Insert Into a Summary Table in Postgres
  • Execute SQL Server Pass-Through Query From Access VBA
  • MS Access SQL Transform Aggregate Manipluation of Values for Pivot
  • SQL "if exists..." dynamic query
  • HANA: Split string?
  • Create a list of dates and match records to the list
  • Why won't SQL round correctly from my batch script?
  • SQLServer - Multiple PIVOT on same columns
  • Update a table foreign keys by substituting by a lookup from the result of another table
  • Deleting rows in a single database table based on the values of other rows
  • Search and replace part of string in database - what are the pitfalls?
  • How could I generate auto insert function SEAT_NO like A1,A2.... B1,B2 in sql server
  • whats a good way to optimize a table? SQL server 2k8
  • Sql server dynamic sequences
  • JPQL Query - object that's in one list and isn't in another
  • How to sort lines in select resquest to don't have multiple same lines
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org