score:3
Accepted answer
Update ThresholdType instead:
update tablename set ThresholdType = 1 - ThresholdType
In case other ThresholdType values might show up later, you can add WHERE ThresholdType IN (1, 2)
, to be a bit safer.
Read More
- Pairwise swapping of rows in sql
- How to concatenate text from multiple rows into a single text string in SQL Server
- Inserting multiple rows in a single SQL query?
- Efficiently convert rows to columns in sql server
- Select n random rows from SQL Server table
- Convert Rows to columns using 'Pivot' in SQL Server
score:1
You can do:
update t
set ThresholdValue = (
select x.ThresholdValue
from t x
where x.DeviceID = t.DeviceID and x.ThresholdType <> t.ThresholdType
)
Result:
DeviceID ThresholdType ThresholdValue
--------- -------------- --------------
1 0 10
1 1 5
2 0 20
2 1 15
See running example at db<>fiddle.
score:3
Just swap the ThresholdType
:
UPDATE t SET ThresholdType = CASE ThresholdType
WHEN 1 THEN 0
WHEN 0 THEN 1
ELSE ThresholdType
END
Execute the query exactly once.
More questions
- How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?
- SQL Server SELECT LAST N Rows
- SQL Server : Columns to Rows
- SQL Server: SELECT only the rows with MAX(DATE)
- SQL statement to select all rows from previous day
- How to delete the top 1000 rows from a table using Sql Server 2008?
- Multiple rows to one comma-separated value in Sql Server
- SQL count rows in a table
- SQL Server: the maximum number of rows in table
- Insert 2 million rows into SQL Server quickly
- SQL Server: combining multiple rows into one row
- Can I have multiple rows of tabs in SQL Server Management Studio
- Delete rows with date older than 30 days with SQL Server query
- SQL Server find and replace specific word in all rows of specific column
- How to update large table with millions of rows in SQL Server?
- How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
- Counting the number of deleted rows in a SQL Server stored procedure
- Deleting 1 millions rows in SQL Server
- Joining a list of values with table rows in SQL
- How to detect duplicate rows in a SQL Server table?
- SQL Server 2008 query to find rows containing non-alphanumeric characters in a column
- How can I insert 100000 rows in SQL Server?
- Removing duplicate rows (based on values from multiple columns) from SQL table
- Return sql rows where field contains ONLY non-alphanumeric characters
More questions with similar tag
- Giving unique identifier to ambiguous parent level when reading XML data
- xp_regread() returned error 5, 'Access is denied.'
- Select query on a partitioned table
- Convert rows to columns with pivoting
- difference xp_sendmail and sp_send_dbmail proc
- Code generation - generating DTOs by referencing database tables.
- why is inserting a single row x times faster than inserting x rows at once
- No MSDTC - Still getting "communication with underlying transaction manager failed"
- Is it a bad idea to create index containing field that changes frequently?
- Which SQL Server edition is suitable for my development?
- Max value grouped by two fields
- DateTime conversion error when querying SQL
- How to update xml in table having multiple same elements in xml, from data by joining another table
- Creating audit triggers in SQL Server
- MAX on Char-Columns in SQL-Server
- How to do nothing in SQL Server
- SSIS - Log to table other than SYSSSISLOG
- Mac alternative to SQL server management studio?
- SQL Combining multiple rows into one
- How do I store date values partially?
- SQL Server: how to generate comma separated string for distinct values in aggregated columns in group by statement
- Scheduling tasks Advice? .Net, SQL Job?
- How to update values in while loop
- Updating sql server table everyday using external webservice
- How do I get 'matched' and 'unmatched' rows from a single table
- Checking for new rows in database - with polling or SQL dependency?
- C# Select in SQL Server just using the Date with sql parameters
- SQLConnection use randomly namedpipes instead of tcp
- Pyodbc: Login Timeout Error
- Examples of SQL injection even when using SQLParameter in .NET?
Source:
stackoverflow.com