June 16, 2014 at 11:56 am
Hi there
im not sure how to do this as im not great with update statements. I have a table that get populated with related ids. basically i want to periodicaly update the table and set the related ids (linkID) to the last know id that was entered, so in the case below
this set
ID | LinkID | Active | Date
z5 | k2 | 0 | 01/01
s6 | k2 | 0 | 17/02
d7 | k2 | 0 | 21/03
d8 | k2 | 0 | 20/04
d9 | k2 | 1 | 02/05
would be updated to this
ID | LinkID | Active | Date
d9 | k2 | 0 | 01/01
d9 | k2 | 0 | 17/02
d9 | k2 | 0 | 21/03
d9 | k2 | 0 | 20/04
d9 | k2 | 1 | 02/05
June 16, 2014 at 11:59 am
ps_vbdev (6/16/2014)
Hi thereim not sure how to do this as im not great with update statements. I have a table that get populated with related ids. basically i want to periodicaly update the table and set the related ids (linkID) to the last know id that was entered, so in the case below
this set
ID | LinkID | Active | Date
z5 | k2 | 0 | 01/01
s6 | k2 | 0 | 17/02
d7 | k2 | 0 | 21/03
d8 | k2 | 0 | 20/04
d9 | k2 | 1 | 02/05
would be updated to this
ID | LinkID | Active | Date
d9 | k2 | 0 | 01/01
d9 | k2 | 0 | 17/02
d9 | k2 | 0 | 21/03
d9 | k2 | 0 | 20/04
d9 | k2 | 1 | 02/05
I can come up with a number of ways to accomplish based on the sample data. Unfortunately I don't really know what you want. What is the logic here? Is it the only active one? The most recent one (and if so how do you know)?
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
June 16, 2014 at 12:38 pm
Im a bit confused if
"I can come up with a number of ways to accomplish based on the sample data" IS true then how can also be "Unfortunately I don't really know what you want" if you don't or cant answer then that is cool just don't bother replying.
But for others, in the SAMPLE data i gave i want to update the ID field with the LAST KNOWN (or most recent one if you like) value from the LinkID field to give the EXPECTED results based on the SAMPLE data i give.
Thanks ever so much
June 16, 2014 at 12:47 pm
ps_vbdev (6/16/2014)
Im a bit confused if"I can come up with a number of ways to accomplish based on the sample data" IS true then how can also be "Unfortunately I don't really know what you want" if you don't or cant answer then that is cool just don't bother replying.
But for others, in the SAMPLE data i gave i want to update the ID field with the LAST KNOWN (or most recent one if you like) value from the LinkID field to give the EXPECTED results based on the SAMPLE data i give.
Thanks ever so much
I don't know what the business rules are supposed to be. I can find a number of queries that will produce the output but that doesn't mean the logic is correct. For example, we could set the ID to the ID for the active row. I don't know if that is what you want or not. Your explanation and sample data are not in synch. You said you want to update linkID but in your sample you updated ID and the values in linkID are the same in every row.
I realize we have a language barrier here but without the rules of the game I can't do much to help here. It is not that I am unwilling or unable to answer. Quite the contrary. I can and will answer once you give us the whole question. Without knowing the business rules the best anybody can do is guess.
_______________________________________________________________
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/
June 16, 2014 at 1:02 pm
Here is a complete shot in the dark.
update [SomeTable]
set ID = s.ID
from
(
select top 1 ID
from [SomeTable]
order by [Date] desc
)s
If that doesn't do it then you need to post ddl and sample data per my previous post.
_______________________________________________________________
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/
June 16, 2014 at 1:13 pm
Thanks for your reply
i guess in a business rules outlook then you could say
If Active=1 (forget about date) then set all the ID's that are linked by the linkID field to the ID value of the row with active=1.
I think this involves a self join but ive never really got my head round them.
Your help is appreciated on this..
June 16, 2014 at 2:56 pm
ps_vbdev (6/16/2014)
Thanks for your replyi guess in a business rules outlook then you could say
If Active=1 (forget about date) then set all the ID's that are linked by the linkID field to the ID value of the row with active=1.
I think this involves a self join but ive never really got my head round them.
Your help is appreciated on this..
Still no ddl and sample data...
Here is my last attempt without the required details.
update [SomeTable]
set ID = s.ID
from
(
select top 1 ID
from [SomeTable]
order by Active, [Date] desc
)s
Don't think you need a self join for this, you just need to use a proper order by. If you don't do something like this how do you know which active row to use when there is more than 1?
_______________________________________________________________
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/
June 17, 2014 at 8:50 am
Thanks that works.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply