Different Update - 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

Transact-SQL (2000)

Different Update

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-29 : 15:38:19

emp_id

1234
4677
6782
5671
3453

emp_details

Name age emp_id dept Date

Greg 35 23455 CS 12/12/04
Abu 38 67658 FR 11/12/04
Raj 33 32788 CS 10/12/04
John 70 76657 CS 12/11/04
Troy 37 67932 EE 12/10/04
Jeff 32 45687 CS 12/09/04
Ben 46 46765 CS 12/06/04
Arthur 35 46466 DW 12/12/04
Mary 33 45645 CS 12/05/04
Dino 38 21256 CS 11/03/06
Zico 26 78890 CS 04/03/04
Owen 17 67415 BA 03/03/04
Figo 67 23445 CS 03/05/04
Zidane 85 56788 CS 07/03/04
Marco 35 45454 CS 09/09/04

Now with the first table I will get the count of records I need to update thats 5 records from emp_id table.
Then I have to pick the corressponding number of records from the emp_details in any order and then update the
selected emp_id(from emp_details table) of that records with the emp_id from the emp_id table and also need to update the date with today's date.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 15:40:12

DDL and DML please. That's CREATE TABLE and INSERT INTO (for sample data) statements. And what it should look like after the query runs.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-29 : 17:21:29

IS there any chance i can get the number of rows into a variable and then use the top statement ot get the records..

Declare @emp_id_count int
set @emp_id_count = (select count(*) from emp_id)

select top @emp_id_count * from emp_details (This statement seems not to work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 17:25:02

No, you'd need dynamic sql for that.

And...select @emp_id_count = count(*) from emp_id instead of your set statement.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-29 : 17:33:01

I guess the emp_id is pretty clear..Here is the one u asked for...please help me with this

CREATE TABLE emp_details
( Name [varchar] (50) NOT NULLL ,
Age[int] NOT NULL ,
emp_id [varchar] (50) NOT NULL ,
Dept [varchar] (50) NOT NULL
Date [datetime] NOT NULL ,

)

insert into emp_details('Greg', 35,'23455', 'CS', 12/12/04)
insert into emp_details('Abu', 38,'256755', 'RE', 12/12/04)
insert into emp_details('Raj', 33,'256125', 'CS', 12/12/04)
insert into emp_details('John', 55,'98855', 'FT', 12/12/04)
insert into emp_details('Troy', 85,'56455', 'CS', 12/12/04)
insert into emp_details('Jeff', 15,'8985', 'TG', 12/12/04)
insert into emp_details('Ben', 75,'43578', 'CS', 12/12/04)
insert into emp_details('Arthur', 65,'78905', 'CS', 12/12/04)
insert into emp_details('Mary', 55,'28975', 'EE', 12/12/04)
insert into emp_details('Dino', 25,'212345', 'CS', 12/12/04)
insert into emp_details('Zico', 75,'28905', 'LM', 12/12/04)
insert into emp_details('Owen', 15,'22345', 'CS', 12/12/04)
insert into emp_details('Figo', 45,'27421', 'CS', 12/12/04)
insert into emp_details('Zidane', 39,'20467', 'CS', 12/12/04)

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-29 : 17:43:50

I tried this and still gives error

Declare @emp_id_count int
select @emp_id_count= count(*) from emp_id

select top @emp_id_count from emp_details

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-29 : 17:55:21

Yes it will give an error. The point was that you should use the format I posted instead of the set statement.

You will need dynamic sql in order to get that code working - top @var1:

http://www.sqlteam.com/item.asp?ItemID=233

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-29 : 21:08:31

This helps to get the top rows but still my problem with update remains..because I need to update these received top rows with the emp_id of the emp_id table.Please do help me

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-29 : 21:16:28

How to make a one to one update..say

emp_id of emp_id table to emp_id of emp_detail table

say we have 3 emp_ids in the emp_id table.I want to select top 3 records from the emp_detail and update the emp_id with the emp_id of the emp_id table.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-30 : 04:14:30

Please this is something important

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-30 : 05:42:04

It might be important....but this service is free (and staffed by volunteers)....so matters get resolved in our 'spare' time......

And you're not helping yourself when you're not following some of the advice given....(as per tduggan)

You might be best advised to break this problem down into smaller steps....try to understand and get working what tara get you....and you should be closer to a solution. we're here to help you help yourself....via education....not for us to do it for you.

it looks like you may be trying to win the 100m in the Athens Olympics despite only starting to walk last week....metaphorically speaking!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources