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

Use CASE & COALESCE
find last balance as on a particular date
Select all days from a table missing some dates
Two related queries needs to be one
How to optimize MERGE using multiple column index
How to use recursive CTE for calculating SUM of a field value hierarchically in SQL Server 2008
Execute Process Task Error
Connecting SQL Server through Network
How can i compare two dates in the same column and get the oldest?
Some of the encoding of hyphen(-) showing the result – and some are showing correct on same page(DB used is Sql server 2008)
How to view the data from SSAS CUBE
How to use SQL Server 2008R2 for working purpose as part of personal use [closed]
Converting my row into column data to column into row in sql server
How to select particular rows partially?
restore only last 1 or 2 months data in sqlserver?
SQL parameter in where clause that allows for all, specific, or multiple values

Categories

HOME
oauth
warnings
label
aem
youtube-livestreaming-api
rocketmq
xtext
dataframe
iso
xlsx
dryioc
nuxt.js
simple-injector
zope
factor-analysis
flat-file
primes
device-detection
rails-activerecord
searchbar
fabric
iis-10
data-cleansing
java-ee-7
rhmap
ab-initio
winscp
invoke-command
roundcube
rowcount
ipfw
large-data
shibboleth
vmd
sql-like
image-compression
parent
ddms
tripwire
dojox.mobile
ttcn
encapsulation
email-parsing
mouseclick-event
spell-checking
rhel6
mbaas
jcreator
date-range
gmt
project-template
keycode
nsfetchedresultscontrolle
easing
wcf-ria-services
excon
ruby-2.0
visible
nofollow
dojox.charting
spring-lemon
faraday
dac
eml
persistence.xml
windows-vista
sourcegear-vault
urn
gray-code
swift2.1
es2015
ticket-system
ejabberd-saas
alphablending
docopt
code-testing
ifft
visual-c++-2010-express
hamsterdb
exiv2
confusion-matrix
facebook-sdk-3.1
layered
nuspec
dynamics-ax-2009
awesomeprint
prng
marmalade-edk
blockingqueue
radchart
scrollto
mpmovieplayer
path-manipulation
datacontract
symbol-server
hardware-acceleration
winverifytrust
web-statistics

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