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

Calculate diffrences between values from diffrent rows in SQL
Automate Data Feeds challenges
How to select rows as a column for View in TSQL?
How to deploy Asp.net MVC4 websit with MSSQL database?
Export Data to Flat File using SSIS Package
SQL Server stored procedure with/without linkserver to self give different result
Insert records to a table with loop from an other table data
SQL Server 2008 - How to fill blank rows with data in other rows (either before or after blank rows)
Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “ATTENDANCERecord”
How to Convert DATETIME to CHAR in MS SQL
Group by using MAX()
How can I get the most recent unique record out of a table not in 1NF?
Want to calculate difference record count in SQL Server 2008
SQL Server 2008 : Where Clause with And Or
Stored Procedure to send different email content to different contacts when it is 30 days before the finish date
SQL Server where clause not working on nvarchar column after database recovery

Categories

HOME
ocaml
translation
json-ld
thunderbird-addon
sequelize.js
i2c
constructor
android-viewpager
eclipse-cdt
uiscrollview
game-physics
automata
scaling
pyephem
flexlm
ng2-dragula
cython
google-maps-android-api-2
servicemix
spring-ws
tflearn
http-method
skmaps
ui5
chrome-native-messaging
i3
thinking-sphinx
bxslider
getjson
read-write
consumer
construct-2
rhino
nsexception
ssh.net
g1gc
intersystems-ensemble
pytest-django
lumen-5.3
azure-availability-set
dimple.js
nstouchbar
dwarf
volume
photography
iphone-developer-program
autoresize
vici
activeweb
tomcat5
contour
spring-repositories
logcat
supportmapfragment
datainputstream
schtasks.exe
findcontrol
pylearn
guzzle6
ember-cli-addons
stackframe
jcr-sql2
ember-components
libssh2
author
facebook-wall
smartystreets
planetary.js
joox
initialization-vector
crystal-reports-10
image-zoom
threadx
vmware-server
ienumerator
mstsc
symphony-cms
fraud-prevention
abnf
snapjs
mvs
pitch
entity-framework-ctp5
selectonemenu
nssavepanel
throttling
posting
cookieless
osx-leopard
surefire
gwt-2.2-celltable
clients
graph-layout
winsnmp
msf
divx

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