August 26, 2011 at 9:04 am
Hey guys,
Im trying to set a flag on table based off a query...But when I tried running the update statement, it updated every row...
UPDATE TABLE tablename
SET columnname = 0
WHERE EXISTS (query)---this is wrong b/c it updated every record in the table
How do I update a column with results from a query...Basically I want every record that gets returned from my query to get updated to 0...
August 26, 2011 at 9:31 am
That isn't how you want to use Exists.
Take a look at these examples. The last one is most likely what you are after.
UPDATE TABLE tablename
SET columnname = 0
WHERE EXISTS (select * from sysobjects where 1 = 1) --this will update every record because there are records
UPDATE TABLE tablename
SET columnname = 0
WHERE EXISTS (select * from sysobjects where 1 = 2) --this will NOT update any record because there are no records
update tablename
set columnname = 0
from tablename
where MyCondition = SomeValue --this will update your table for records when the Where condition is met
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 9:45 am
Ok let me try to explain this better...
I have written a query and it returns me a result set of 4000+ records...I want to flag those records...How can I update the flag column to 1 for just those 4000+ records out of a 15000+ records table?
August 26, 2011 at 9:48 am
Look at the third example above. That is exactly what you need. Or if it is just a single table just execute an update statement with the same where clause as your select.
Update MyTable
set MyColumn = MyValue
where MyConditionColumn = ConditionToBeMet
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 10:12 am
I apologize Im not understanding the where clause...what is the condition clause = condition to be met...all i have is a SELECT query
August 26, 2011 at 11:10 am
You have a where clause on your select? Add the same where clause to the update. If you post your select I can show you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 12:49 pm
No I dont have a WHERE clause in my SELECT query...I want to update table A...This query returns a result set and want to update those columns that this query returns...
SELECT * FROM table A d
INNER JOIN table B m ON m.LastName = d.LastName AND
m.Address1 = d.Address1 AND
m.Address2 = d.Address2 AND
m.Address3 = d.Address3 AND
m.City = d.City AND
m.State = d.State AND
m.Zip = d.Zip AND
m.FBNumber = d.FBNumber
August 26, 2011 at 1:06 pm
asm1212 (8/26/2011)
No I dont have a WHERE clause in my SELECT query...I want to update table A...This query returns a result set and want to update those columns that this query returns...SELECT * FROM table A d
INNER JOIN table B m ON m.LastName = d.LastName AND
m.Address1 = d.Address1 AND
m.Address2 = d.Address2 AND
m.Address3 = d.Address3 AND
m.City = d.City AND
m.State = d.State AND
m.Zip = d.Zip AND
m.FBNumber = d.FBNumber
OK so using the example #3 from my first post.
update tableA
set SomeColumn = 1
FROM tableA d
INNER JOIN tableB m ON m.LastName = d.LastName AND
m.Address1 = d.Address1 AND
m.Address2 = d.Address2 AND
m.Address3 = d.Address3 AND
m.City = d.City AND
m.State = d.State AND
m.Zip = d.Zip AND
m.FBNumber = d.FBNumber
That get you what you need?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 1:11 pm
Kind of but not completely...If I run that SELECT statement as is, there is 4458 records...
But when I ran it with the UPDATE statement - it only changed 3906 records...So some did not get updated...but we are getting there!
August 26, 2011 at 1:16 pm
Does the select perhaps return rows from TableA multiple times due to the join? If so, those will only be updated once.
How many rows does this return?
SELECT DISTINCT d.*
FROM tableA d
INNER JOIN tableB m ON m.LastName = d.LastName AND
m.Address1 = d.Address1 AND
m.Address2 = d.Address2 AND
m.Address3 = d.Address3 AND
m.City = d.City AND
m.State = d.State AND
m.Zip = d.Zip AND
m.FBNumber = d.FBNumber
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 1:22 pm
Yep that was it...Thank yall so much!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply