select highest value. - 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

SQL Server 2000 Forums

SQL Server Development (2000)

select highest value.

Author

Topic

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-28 : 02:44:47

Hi guys, do u guys have any idea to simplifies this query? Thanks

UPDATE table1
SET table1.date= table2.date
FROM table1,table2
WHERE table1.col2 = table2.col1 AND version= (select max(version) from table1 where table1.col2 = = table2.col1 )

INSERT INTO table1(column names)
SELECT col1,col2,'YES','FEMALE'
FROM table2,table1
WHERE table1.col2 = table2.col1 AND version= (select max(version) from table1 where table1.col2 = = table2.col1 )

i need to adjust the highest version in the table if the table1.col2 and table2.col1 are matched.

Table like this
table1
=======
col1 | col2 | version
abc----ghi-------1
def----jkl-------1
abc----ghi-------2 <---i want to edit this row..based condition from table2,put in date when i last changed it.
abc----ghii-------3 <---and want to insert this row..updated version

can get me ka?

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-28 : 04:36:32

pleaseee???

*blink*blink*

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 05:49:51

Don't quite understand what you need as your query and the table sample does not match.
Can you post your table DDL, some sample data and the expected result ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-29 : 03:49:33

Hi,

What i wanted to do is..

There is 2 tables, one is old..one is updated (new)..i want to update the old table..

so..it going to look for highest version in old table, and try to match it to the new table. If theres a match, then it goin to update the latest row (highest version)in the old table, with current date (theres a date column in the old table,it gets the date from the new table) and it going to insert new row inside the old table with matched information from the new table.

follow me?

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-29 : 07:39:55

i tryin to run this script, but some how doesnt come out what i expected. the first part (update+insert into) it should modify 109 rows (i check the table1.col1 = table2.col1) but somehow only modify 100 rows.

and the worst part is,2nd insert,inserting 100 duplicated rows.

what im doing wrong here? please help.

==soc==
Begin
UPDATE table1
SET table1.sub_to_dttm = convert(datetime,(left(table2.update_date,8)+' '+substring(table2.update_date,9,2)+':'+substring(table2.update_date,11,2)+':'+substring(table2.update_date,13,2)),113)
FROM table1,table2
WHERE table1.col1 = table2.col1 AND version = (select max(version) from table1 WHERE table1.col1 = table2.col1)

INSERT INTO table1(column names)
SELECT col1,col3,'PRE',col2,'2',convert(datetime,(left(table2.update_date,8)+' '+substring(table2.update_date,9,2)+':'+substring(table2.update_date,11,2)+':'+substring(table2.update_date,13,2)),113),'1/1/9999 11:59:59 PM','N',(table1.version+1),'1'
FROM table2,table1
WHERE table1.col1 = table2.col1 AND version = (select max(version) from table1 WHERE table1.col1 = table2.col1)

INSERT INTO table1 (column names)
SELECT col1,col3,'PRE',col2,'2',convert(datetime,(left(table2.update_date,8)+' '+substring(table2.update_date,9,2)+':'+substring(table2.update_date,11,2)+':'+substring(table2.update_date,13,2)),113),'1/1/9999 11:59:59 PM','N','1','1'
FROM table2,table1
WHERE table1.col1 <> table2.col1

End
==eoc==

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-29 : 08:47:43

mrjack - did you see khtan's request?

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-29 : 22:33:13

the sample data is big..one row is 39 columns...ok..will try my best..

Table update(new)..ill just show the columns im interested.
=============================================================

sub_id,member_number,update_date,sub_number,status,remarks,brand

100,0123456789,'20060304112935',502191300384190,2,'remarks','Brand'

Main Table (the one will be updated)
====================================

sub_id,sub_sub_id,sub_mmember_number,sub_membership_type,sub_brand,sub_status,sub_from_date,sub_to_date,version)

1031,(same as above table sub_number),(same as above table member_number),'PRE',(same as above table brand),'2','1/1/2000','1/1/9999 11:59:59 PM','1'

So...firsty,

for all row in update table, it will check for member_number and sub_number match,with max sub_version_id..

then it will update the sub_to_date..with the update_date from update table...then it will duplicate the row in the main table..with all the info with only different is sub_from_date,sub_to_date and increment version(plus 1)

for all row that doesnt match member_number and sub_number will be inserted one row..

ahh..can get me?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-30 : 03:22:33

mrjack - Okay, that's the DDL and some sample data. Now post the expected result... i.e. what the update and main tables will look like once the process is complete...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-30 : 03:39:49

well..i managed to get the script to work..but one thing that confuse me..

table1 = 5000 rows
table2 = 6000 rows

table1.col1=table2.col1 = 4500 rows
table1.col1<>table2.col1 = 10000 rows, it should come out as 500 rows..

what i did wrong this time?

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-30 : 03:40:03

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-30 : 03:46:41

I don't know - I don't understand your problem yet...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 04:42:00

quote:
Originally posted by mrjack

well..i managed to get the script to work..but one thing that confuse me..

table1 = 5000 rows
table2 = 6000 rows

table1.col1=table2.col1 = 4500 rows
table1.col1<>table2.col1 = 10000 rows, it should come out as 500 rows..

what i did wrong this time?

Read this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=63962

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources