UPDATE with CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE with CTE

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-04 : 17:38:15

Hi guys,

I want to flag col = 'Status' with message 'void' for duplicate values in My_table.

I have tried:

WITH CTE_Duplicates AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY (SELECT 0)) RN
FROM my_table
WHERE col1 = 'A'
)

UPDATE my_table
SET Status = 'void'
from CTE_Duplicates2 where RN<>1

For a reason , its not flagging duplicate values, It is infact flagging all value where col1 = A.
Please note that I just want to mark "void" only for duplicate results.
Thanks in Advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-04 : 18:25:03

Update the CTE itself:

WITH CTE_Duplicates AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY (SELECT 0)) RN,
[Status]
FROM my_table
WHERE col1 = 'A'
)

UPDATE CTE_Duplicates 
SET Status = 'void'
where RN<>1

You can update a CTE subject to some rules and regulations (no recursion, no aggregates etc. in the CTE).

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-04 : 19:28:06

Works Fine. Thanks sunita :)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-07 : 12:39:46

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names, which you do not know.

>> I want to flag col = 'Status' with message 'void' for duplicate values in My_table. <<

Based on your vague narrative, tot6al lack of DDL and highly proprietary code, this generic, magic “status” is a non-relational Boolean flag. In a valid data model, we can have <something>_status” not a generic.

We also have no idea how “duplicate' is defined; on all columns or just a subset? Why is there no UNIQUE constraint to prevent the prevent the problem? Do you need to keep a count of occurrences?

>> For some reason, it is not flagging duplicate values, It is in fact flagging all value where col1 = A. <<

Of course! SQL is a set-oriented language that uses predicates. You are still writing code for a sequential magnetic tape file system. Sunita gave you a kludge that will only make more problems later.

You have no idea how RDBMS works and need to stop programming until you do; you will hurt your company.

Now, quit being rude, post the DDL and clear specs.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources