help on updating a field in a table with the field content of another table

HI everybody need help on this..

I have two tables below

table1

country countryid

africa ___
usa ___
italy ___
Spain ___

table2

countryid country name

1 africa
2 germany
3 italy
4 usa

I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..

THE RESULT WOULD BE

country countryid

africa 1
usa 4
italy 3
spain 0

thanksShow us what you've come up with so far.|||yes trying this sql and it executed well and updated the first table

UPDATE table1
SET countryid=(SELECT countryid FROM table2
WHERE cntryname=country)

thanks anyway|||the solution worked fine until it didn't encounter a duplicate value... since it is a 1 to many relationship it gave me this error...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

thanks again|||you mean you have two different countries with the same name? which ones? my geography skills aren't as good as I thought... :)|||use JOIN, something like -

UPDATE table1
SET countryid= COALESCE(table2.countryid, 0)
from table1
left outer Joint table2
ON
table2.cntryname=table1.country|||alexyeth,
I think the problem is that "africa" is not a country but continent.
:)|||thanks everybody.. the solution of mihir is great using the coalesce function...

Help on Showing Querry Execution Plan in SQL Server Management Studio

Hi Everybody,
I am trying to run a querry (calling a sp) in SQL Server Management
Studio and have it display the Execution Plan.
When I try to do this SQL Server creates a system tempdb table that
eats up all my physical memory (goes over 20 + gig and keeps growing
until the computer can't take it anymore and dies... I actually have
to manually get rid of that table for my computer to ba able to
runnormally again).
I am doing some research online about this but so far can't find
anything...
Is there a way I can avoid SQL server from creating that tempdb table
when I want to show the execution plan for a querry? or is there any
setting I can modify to avoid that tempdb table to keep growing?
Thanks a lot,
Taslim
PS: My computer is a P4 3.06 Ghz, 1.25 gig ram and has 24 gig free
physical memory...sqlserver uses tempdb for its workspace. so, the answer is 'no'.
from the sound of your description, this must be some sproc that does tons
of dml operations!
-oj
<taslimn@.gmail.com> wrote in message
news:1149616073.134649.115920@.h76g2000cwa.googlegroups.com...
> Hi Everybody,
> I am trying to run a querry (calling a sp) in SQL Server Management
> Studio and have it display the Execution Plan.
> When I try to do this SQL Server creates a system tempdb table that
> eats up all my physical memory (goes over 20 + gig and keeps growing
> until the computer can't take it anymore and dies... I actually have
> to manually get rid of that table for my computer to ba able to
> runnormally again).
> I am doing some research online about this but so far can't find
> anything...
> Is there a way I can avoid SQL server from creating that tempdb table
> when I want to show the execution plan for a querry? or is there any
> setting I can modify to avoid that tempdb table to keep growing?
> Thanks a lot,
> Taslim
> PS: My computer is a P4 3.06 Ghz, 1.25 gig ram and has 24 gig free
> physical memory...
>

Help on Showing Querry Execution Plan in SQL Server Management Studio

Hi Everybody,
I am trying to run a querry (calling a sp) in SQL Server Management
Studio and have it display the Execution Plan.
When I try to do this SQL Server creates a system tempdb table that
eats up all my physical memory (goes over 20 + gig and keeps growing
until the computer can't take it anymore and dies... I actually have
to manually get rid of that table for my computer to ba able to
runnormally again).
I am doing some research online about this but so far can't find
anything...
Is there a way I can avoid SQL server from creating that tempdb table
when I want to show the execution plan for a querry? or is there any
setting I can modify to avoid that tempdb table to keep growing?
Thanks a lot,
Taslim
PS: My computer is a P4 3.06 Ghz, 1.25 gig ram and has 24 gig free
physical memory...sqlserver uses tempdb for its workspace. so, the answer is 'no'.
from the sound of your description, this must be some sproc that does tons
of dml operations!
--
-oj
<taslimn@.gmail.com> wrote in message
news:1149616073.134649.115920@.h76g2000cwa.googlegroups.com...
> Hi Everybody,
> I am trying to run a querry (calling a sp) in SQL Server Management
> Studio and have it display the Execution Plan.
> When I try to do this SQL Server creates a system tempdb table that
> eats up all my physical memory (goes over 20 + gig and keeps growing
> until the computer can't take it anymore and dies... I actually have
> to manually get rid of that table for my computer to ba able to
> runnormally again).
> I am doing some research online about this but so far can't find
> anything...
> Is there a way I can avoid SQL server from creating that tempdb table
> when I want to show the execution plan for a querry? or is there any
> setting I can modify to avoid that tempdb table to keep growing?
> Thanks a lot,
> Taslim
> PS: My computer is a P4 3.06 Ghz, 1.25 gig ram and has 24 gig free
> physical memory...
>sql

help on find duplicates and delete procedure

Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.

my PERSON table is this below:

ID name fname ownerid id2

1 a b
2 c c
3 e f
4 a b 1 10
5 c c 2 11

I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname

select * from ( Select name ,fname, count(1) as cnt from PERSON group by
name,Fname ) where cnt > 1

ID name fname ownerid id2

1 a b
4 a b 1 10

2 c c
5 c c 2 11

With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..

thanks
alexhow do you know which one is the "second" record?

and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?|||how do you know which one is the "second" record?

and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?

the second record is the one with ownerid and id2 well the reason behind is that the second record is inserted from another table and the id of the first one is the one i need due to relationships with other tables|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.

yes thanks for your suggestion will try it then...|||this might work for u ...
[code]
delete from Table1 where ID in
(select ID from Table1, (Select name ,fname, count(*) as cnt from Table1 group by name,Fname having count(*) > 1) as xx
where Table1.name=xx.name and Table1.fname=xx.fname and Table1.ownerid is not null and Table1.id2 is not null)
[code]

help on comparing and modifying a resultset or table

HI everybody need help badly on the following

have to recordsets with the following with the following SQL

SELECT place, count(minus)as countminus
order by place
group by place

THE RESULT WOULD BE

PLACE countminus

ABC 10
DEF 50
HIJ 5
KLM 2
MNO 0

MY OTHER QUERY IS
SELECT plus, count(plus) as countplus
GROUP BY plus
ORDER BY plus

THE RESULT WOULD BE:

PLUS COUNTPLUS

ABC 10
DEF 20

my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...

with this condition..

I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...

THE RESULT WOULD BE THIS....

PLACE countminus COUNTPLUS

ABC 10 10
DEF 50 20
HIJ 5 0
KLM 2 0
MNO 0 0

I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYSPLEASE STOP THINKING ABOUT LOOPS!!!! SQL DOESN'T WORK THAT WAY!!!!
select m.place
, m.countminus
, coalesce(p.countplus,0) as countplus
from (
select place
, count(minus) as countminus
from sometable1
group by place
) as m
left outer
join (
select plus
, count(plus) as countplus
from sometable2
group by plus
) as p
on p.plus = m.place|||thanks so much it solved my problem sorry for the loop frase i mentioned...

again thanks

Help me to write this query

Hi Everybody,
I have a table call Employee like this
EmpId DOB Sex
1 01/02/1982 Male
2 01/01/1983 Female

And also i have got Contacts Table looks like this
ConId EmpId ConDate Description
1 1 02/02/2007 Not Specified
2 1 02/03/2007 Not Specified
3 2 01/01/2007 Personal

Now i need to display like this

Description Male Female Age 0-10 Age 10+
Not Specified 1 0 0 1
Personal 0 1 0 1

When calculation the age If one employee has got more than one contacts then we need to get the maximum ConDate from the Contacts table and then get the DOB from the Employee table and and get the DateDiff by year

How can can i do this query ?
Any Idea ?

regards
suis

Here You go..

Code Snippet

Create Table #employee (

[EmpId] int ,

[DOB] datetime ,

[Sex] Varchar(100)

);

Insert Into #employee Values('1','01/02/1982','Male');

Insert Into #employee Values('2','01/01/1983','Female');

--Completed :: employee

--Sample Table :: employeecontact

Create Table #employeecontact (

[ConId] int ,

[EmpId] int ,

[ConDate] datetime ,

[Description] Varchar(100)

);

Insert Into #employeecontact Values('1','1','02/02/2007','NotSpecified');

Insert Into #employeecontact Values('2','1','02/03/2007','NotSpecified');

Insert Into #employeecontact Values('3','2','01/01/2007','Personal');

--Using SQL Server 2000

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join (Select

EC.EmpID,EC.Description

From

#employeecontact EC

Join (

Select

[EmpId]

,Max([ConDate]) ConDate

From

#employeecontact

Group By [EmpId]

) as Data On Data.ConDate =EC.ConDate and Data.EmpID = EC.EMPID) C On E.EmpID=C.EmpID

--Using SQL Server 2005

;With CTE as

(

Select EmpId,Description, ROW_NUMBER() Over (Partition By EmpId Order By [ConDate] Desc) RowId From #employeecontact

)

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join CTE C On E.EmpId = C.EmpId

Where

C.RowID=1

|||

Code Snippet

create table #emp

( EmpId int, DOB datetime, Sex varchar(10))

insert into #emp values (1, '01/02/1982', 'Male')

insert into #emp values (2, '01/01/1983', 'Female')

insert into #emp values (3, '10/01/1999', 'Female')

create table #contacts

( ConId int, EmpId int, ConDate datetime, Description varchar(100))

insert into #contacts values (1, 1, '02/02/2007', 'Not Specified')

insert into #contacts values (2, 1, '02/03/2007', 'Not Specified')

insert into #contacts values (3, 2, '01/01/2007', 'Personal')

insert into #contacts values (3, 3, '5/01/2007', 'Personal')

;with contact1 as

(

select c.EmpId, c.Description, max(c.ConDate) as ConDate

from #contacts c

group by c.EmpId, c.Description

),

contact2 as

(

select c.EmpId, c.Description, c.ConDate, e.Sex,

datediff(yy, e.dob, c.ConDate)

+ case when (month(e.dob)*100)+day(e.dob)>(month(c.ConDate)*100)+day(c.ConDate)

then -1 else 0

end as Age

from contact1 c

inner join #emp e

on c.EmpId = e.EmpId

)

select Description,

sum(case Sex when 'Male' then 1 else 0 end) as Male,

sum(case Sex when 'Female' then 1 else 0 end) as Female,

sum(case when Age <=10 then 1 else 0 end) as 'Age 0-10',

sum(case when Age > 10 then 1 else 0 end) as 'Age 10+'

from contact2 c

group by Description

|||Hi Manivannan.D.Sekaran
Many Many thanks for your quick response.this is great help for me,
i will have a look this query and let u know ASAP the result,cos this query is bit tau ff for me,

again thank you so much for your valuable response.

this is great forum.....i love this forum........

regards
suis
|||hi Dale and Sekaran
Its very useful u r comments to sort out my problem
now my query is working perfectly,i had to do some modification for that,becase my database structure is not like as i post ,becase its just sample database format,

anyway thanks for the quick response from you two .

many thanks to .Net forum......nice work

regards
suis

Help me please! (production DB marked as suspicious)

Hi everybody,

DBMS - MS SQL 6.5
I have removed physical files: Name.DAT and NameLog.DAT
from folder and put older files there with the same names.
After restarting the engine has marked my DB as suspicious.

Then I moved previous files back - database still suspitious!!!

I have no idea how to restore data base status after removing phisical files?

I need help as my MS SQL 6.5 marked as suspicious my production
database.If you can replace the DB by this way,why MS give some backup,restore function?|||If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.

Cheers|||Sorry, I just noticed that you are on version 6.5. I'm not sure if the info will be valid for that version, you will have to try it to find out.

Thanks,

Originally posted by bmalar
If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.

Cheers|||True you should use SP_RESETSTATUS, before that use:

sp_configure 'allow updates',1
go
reconfigure with override
go

After the procedure is created, immediately disable updates to the system tables.
sp_configure 'allow updates',0
go

reconfigure with override
go

... then use SP_RESETSTATUS and reboot the server.

Help me Please to Create this TRIGGER

Hi everybody,

How can I Update a field from another table by Trigger? Can someone send me the statment to do it?

I have a table called Clients with fields : ID_Clients, Client
And Another called Doc with fields : ID_Doc, ID_Clients, Client

These tables are in different databases and I would like to esure the integrity by add a Trigger to update in Docs table the field Client everytime its changed in the Clients table.

Thanks for Attetion.

Leonardo AlmeidaThis is not a forum for tutorials.

Here is some sample code:

CREATE TRIGGER YourTrigger ON dbo.YourTable
FOR UPDATE
AS
update DBName.DBTable
set YourField = inserted.YourField
from DBName.DBTable
inner join inserted on DBName.DBTable.KeyField = inserted.KeyField

Please read about triggers in Books Online and then post again if you have a specific question.

blindman

Help me create this Trigger

Hi everybody,

How can I Update a field from another table by Trigger? Can someone send
me the statment to do it?

I have a table called Clients with fields : ID_Clients, Client
And Another called Doc with fields : ID_Doc, ID_Clients, Client

These tables are in different databases and I would like to esure the
integrity by add a Trigger to update in Docs table the field Client
everytime its changed in the Clients table.

Thanks for Attetion.

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Leonardo Almeida" <leonardoalmeida2004@.yahoo.com.br> wrote in message
news:3f672aa2$0$62077$75868355@.news.frii.net...
>
> Hi everybody,
> How can I Update a field from another table by Trigger? Can someone send
> me the statment to do it?
> I have a table called Clients with fields : ID_Clients, Client
> And Another called Doc with fields : ID_Doc, ID_Clients, Client
> These tables are in different databases and I would like to esure the
> integrity by add a Trigger to update in Docs table the field Client
> everytime its changed in the Clients table.
> Thanks for Attetion.
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Something like this should work:

create trigger dbo.ATR_U_Clients
on dbo.Clients
after update
as

if @.@.rowcount = 0
return

update OtherDatabase.dbo.Doc
set Client = i.Client
from OtherDatabase.dbo.Doc d
join inserted i
on d.ID_Clients = i.ID_Clients

Simon