sql-server-2008


How to convert select statement to an update statement


I'm trying to create an Update statement that works or functions the same as this Select statement:
SELECT *
FROM table
WHERE field1 = 1
AND field2 NOT IN (SELECT field2
FROM table
WHERE field1 = 0)
The translation from select to update is very simple in this case as it doesn't require SQL Server's non standard proprietary update syntax.
UPDATE table
SET field3 = 'X'
WHERE field1 = 1
AND field2 NOT IN (SELECT field2
FROM table
WHERE field1 = 0)
Another way of translating it would be to just put the whole select statement inside a cte and update that.
WITH t
AS (SELECT *
FROM table
WHERE field1 = 1
AND field2 NOT IN (SELECT field2
FROM table
WHERE field1 = 0))
UPDATE t
SET field3 = 'Y'
If any of the columns involved are nullable not exists will likely have a better plan though.
UPDATE [Table1]
SET [Column1] = ?
,[Column2] = ?
--,... other columns and so on....
WHERE field1 = 1
AND NOT EXISTS (SELECT 1
FROM Table2
WHERE field1 = 0
AND Field2 = [Table1].Field2 )

Related Links

Creating and using synonyms in a stored procedure
How to run a SSIS Package which has a file based source and a target
Subquery returned more than one value
Sql Update query to update a column in an existing table to have unique value which has duplicate values
Passing a User defined table type to SP without declaring
Select Last Inserted Record in SQL Server 2008 using vb.net SqlCommand with CommandType set to Text “NOT USING ANY STORED PROCEDURE” [duplicate]
SQL Server: Bulk load data conversion error
Combine multiple records to one
Find minimum and maximum column value of a common column across all tables in a SQL Server 2008 database
how to search for a given sequence of rows within a table in SQL Server 2008
SQL Server 2008 - Transparent Data Encryption - APIs to read backup logs
Show a sum of the column at the end of the column in mssql 2008
get startdate and enddate from specified week of the month
Time difference for each row in group
How to convert a 5 digit INT used as time and display it as HH:MM:SS in SQL
SQL Server - To find member with the highest fine charged (based on below scenario)

Categories

HOME
security
openssl
crate
vb6
rotation
drupal-7
formal-verification
computer-vision
ruby-on-rails-3
automated-tests
apache2
ios10
phaser-framework
pygame
currency
android-viewpager
google-classroom
gwtp
lenskit
sendkeys
lstm
reduction
rhapsody
plupload
tweets
cruisecontrol.net
google-ima
pdfsharp
bus-error
encase
skygear
graph-databases
large-data
viewstate
jboss-esb
bing-translator-api
microdata
estimation
dimple.js
workflow-foundation-4.5
dwarf
scd
qos
iron.io
gulp-typescript
objloader
tomee
rmongodb
galleriffic
wcf-ria-services
pax-web
search-regex
git-rebase
matcaffe
anti-patterns
on-duplicate-key
harp
graphical-logo
epl
pgagent
omnifocus
bullet
neoload
voldemort
bignum
bigint
dllexport
qt4.6
git-filter-branch
osi
macruby
shim
emacs23
cross-domain-policy
fileutils
shared-objects
cookieless
nvelocity
communicationexception
outlook-form
data-execution-prevention
visual-studio-dbpro
associativity
dotproject

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App