SQL Query Syntax

If I have a table with these fields:
TableName       ColumnName    Rank
Employee         EmployeeID      1
Employee         EmployeeID      1

How do I update this table such that if TableName.ColumnName in the 2 records are the same, then update the Rank column in the second record to Rank+1 i.e. 2. The output should be:
TableName       ColumnName    Rank
Employee         EmployeeID      1
Employee         EmployeeID      2

If it is not the case, then leave the table as it is.
spiroseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
Do you want to get consequtive numbers in Rank filed?
what value do you want if existing Rank values are 2 and 2 in both records?
what value do you want if existing Rank values are 2 and 3?

if you just want seuential numbers you can try using row_number() function as follows

Select TableName,       ColumnName, row_number() over(order by TableName, ColumnName) as RANK
from table
0
spiroseAuthor Commented:
The existing Rank column in the table TableA is from another TableB

select distinct A.TableName, A.ColumnName, rank() over (partition by Table_Name, ColumnName) AS Rank
from
TableB


Now when In TableA,
the TableNAme and ColumnName are the same, I need a way to distinguish the 2 records by updating the rank column in TableA (as long as they are different). Also same would be the case if there were 3 records in TableA with the same dilemma and so on...

0
spiroseAuthor Commented:
Sorry, typo in the Alias, this is what I meant:


select distinct B.TableName, B.ColumnName, rank() over (partition by Table_Name, ColumnName) AS Rank
from
TableB B


Now when In TableA,
the TableNAme and ColumnName are the same, I need a way to distinguish the 2 records by updating the rank column in TableA (as long as they are different). Also same would be the case if there were 3 records in TableA with the same dilemma and so on...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

appariCommented:
problem still not clear. can you explain with sample data from both the tables, how they are linked and the output you want.
0
keyuCommented:
You must need one unique field for updation so add one autonumber field in your table and perform below query....

TableName       ColumnName    Rank id
Employee         EmployeeID      1         1
Employee         EmployeeID      1         2

Update table set rank=rank+1 where id=(select top id from table where tablename='Employee' and Columnname='EmployeeID' order by id desc)
0
spiroseAuthor Commented:
Let me re-explain my problem.
There is a Stored Proc which takes @OrderID as a param. If we pass a value of 3 to @OrderID, these values are populated on these tables (no append but truncate and insert every time the proc is run)

TableMain1
OrderID  TableDefinition                         SeqID  SampleID
1            HumanResources.Employee       43              5
2            HumanResources.Employee       43              5

TableMain2
TableSchema        TableName   InputColumnName  OutputColumnName SampleID SeqID OrderID
HumanResources  Employee     EmployeeID            EmployeeID             5              43             3
HumanResources  Employee     EmployeeID             EmployeeID_first     5              43             3


#TableCompare
TableSchema        TableName     InputColumnName   OutputColumnName    Rank   SampleID  ORderID
HumanResources   Employee       EmployeeID             EmployeeID                  1       5            3
HumanResources   Employee       EmployeeID             EmployeeID_first           1       5            3

#TableCompare is created from TableMain1 A inner join TableMain2 B on A.TableDefinition = B.TableSchema+B.TableName and A.SeqID = B.SeqID where B.OrderID = @OrderID

I need to update the field OutputColumnName in #TableCompare to:
HumanResources   Employee       EmployeeID       OrderID1.EmployeeID                  1       5            3
HumanResources   Employee       EmployeeID       OrderID2.EmployeeID_first           1       5            3

The values OrderID1 and OrderID2 are taken from TableMain1. How do I achieve this?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
spiroseAuthor Commented:
Sorry I am still not clear on how to do this.
0
LowfatspreadCommented:
#TableCompare is created from TableMain1 A inner join TableMain2 B on A.TableDefinition = B.TableSchema+B.TableName and A.SeqID = B.SeqID where B.OrderID = @OrderID

that should give you 4 rows not 2....

why don't you take the orderid from table1 at this time...?
0
spiroseAuthor Commented:
This is how #TableCompare is created

select distinct TableSchema, TableName, InputColumnName, OutputColumnName, SampleID, ORderID, Rank
into #TableCompare
from
(select distinct TableMain2.TableSchema, TableMain2.TableName, TableMain2.InputColumnName, TableMain2.OutputColumnName,  TableMain2.SampleID, TAbleMain2.ORderID
RANK() OVER (PARTITION BY TableSchema, TableName ORDER BY InputColumnName) AS RANK
FROM
TableMain1 A inner join TableMain2 B on A.TableDefinition = B.TableSchema+B.TableName and A.SeqID = B.SeqID where B.OrderID = @OrderID
) AS x



Also, we need to grab the OrderID from TableMain2 to begin with in #TableCompare as a requirement..
0
LowfatspreadCommented:
you cant do that...

you haven't got enough information in table2 (and/or table1) to enable you to join row1 of table1 with row1 of table2
and row2 to row2....

you'd need some sort of sequence number...

would you like to take a step back and try to explain what it is that you are trying to achieve...?
0
spiroseAuthor Commented:
Can I add that sequence number in those tables - Table1 and Table2. And if I were to do that, how would I achieve the outcome....
0
LowfatspreadCommented:
yes - you could add a sequence number

but i don't understand your design/requirements so cant develop the idea/method further ...
0
awking00Commented:
>>The existing Rank column in the table TableA is from another TableB<<

Can you change this -
select distinct A.TableName, A.ColumnName, rank() over (partition by Table_Name, ColumnName) AS Rank
from
TableB

to this?
select distinct A.TableName, A.ColumnName, row_number() over (partition by Table_Name, ColumnName order by rowid) AS Rank
from
TableB

0
awking00Commented:
Sorry I was thinking Oracle and not MS SQL Server (no rowid)
select distinct A.TableName, A.ColumnName, row_number() over (partition by Table_Name, ColumnName order by 1) AS Rank
from
TableB
0
LowfatspreadCommented:
but until we understand your design requirements that doesn't guarantee what you require...

0
spiroseAuthor Commented:
#TableCompare table has the join information only (OrderID 3  for our sampleID is a JOIN) in a SQL Query. All information regarding type of join, join columns etc is stored in OrderID 3 in TableMain1 which we then put in a temporary table #TableCompare. The OutputColumnName is the same as an Alias Name for the columns. We need to update the table #TableCompare ultimately to show, #Step1TableName.InputColumnName AS AliasName for the first record. #Step2TableName.InputColunName as OutputColumnName for the second record. Te #s 1 and 2 thus come from TableMain1 described below:

TableMain1 stores the information for all the actions in a SQL Query as a separate record in the table. So OrderID 1 is the first source table, OrderID 2 is the second source table and so on

0
LowfatspreadCommented:
so shouldn't table1 have an alias name for the table....

and table2 should just reference that alias....

then you generate the from clause table expressions  like this

(select inputcolumnname as outputcolumnname
         ,.....
   from table_schema.tablename
  where ....
 ) as Tablealias1
join
(select inputcolumnname as outputcolumnname
         ,.....
   from table_schema.tablename
  where ....
 ) as Tablealias2
on tablealias1.outputcolumnname=tablealias2.outputcolumnname
and tablealias1.outputcolumnname=tablealias2.outputcolumnname


you may not have a where clause for the subqueries...
i'm not clear how your choose between your join tyoes
or indicate the conditions between them or even how you identify that a particular column from one table
is to be compare to another....


your design appears to be both overly simplex and complex... but its difficult to know with such patchy information on the scope and requirements of the system or its ultimate goals...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spiroseAuthor Commented:
Table1 does not hold Alias names. It is designed to be based on minimal information not listing the column or alias names just TableSchema and TableName Table2 does that. Letme try your solution and get back to you.
0
spiroseAuthor Commented:
I am going to post a question trying to better clarify my dilemma.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.