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

SQL Server 2008 Column Identity based on years
same query twice but different from clause
Count number of Distinct days in query
Create a top 3 leader table with tie'd scores
Convert MS Access Crosstab to SQL Server 2008
TSQL First of last month - date only
Why I can't declare a table variable together with others declarations?
Is it possible to set current date in stored procedure parameter?
Restict SQL Server access for certain application
How do I overcome Microsoft SQL Server, Error: 1827: restore failed…exceed licensed limit?
How can I reference an aggregate column in another column in the same query?
To calculate the percentage of items for various item types using SQL Server 2012
How do I pass multiple parameters in sql cursor
Using Case in where statement with user input
How can I script SQL Server database diagrams?
How to get SqlCommand execution Result to log file ?

Categories

HOME
python
sass
project-management
amazon-swf
system-verilog
bing
json-ld
survival-analysis
appmaker
recyclerview
azureservicebus
dropbox
hashicorp-vault
intellij-plugin
tee
viber
line-api
eclipse-luna
cross-platform
arm-template
ms-access-2007
chocolatey
nmake
os161
tarantool
header-files
sumo
pptp
web-frontend
devforce
jboss5.x
percentage
spring-profiles
postgresql-9.2
exponential
shibboleth
socketscan
dart-pub
tango
taglib
stress-testing
email-parsing
openweathermap
archer
data-integration
drawingarea
goquery
component-pascal
android-bitmap
mcrypt
rxvt
onresume
keycode
sql-import-wizard
picasa
gemini
selecteditem
anti-cheat
fill
eml
git-rebase
datainputstream
android-snackbar
ffprobe
coypu
ibm-data-studio
virus
hg-git
orientation-changes
ibmsbt
mov
genetic-programming
power-law
argument-passing
ssms-addin
broadcasting
json-patch
operations
confusion-matrix
bigint
swrl
stagefright
reporting-tools
sametime
pys60
ubuntu-11.10
xmlslurper
msgbox
wchar
path-manipulation
objectbrowser
datacontract
scalaxb
rijndael
gwt-2.2-celltable
gears
associativity

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