update 2 tables - 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)

update 2 tables

Author

Topic

ForceF16
Starting Member

13 Posts

Posted - 2007-02-28 : 22:24:49

Hi,

I want to update from table1 to table 2

Table 1

User_name (full name:firstname, Middle, lastname)
User_email

Table 2
FirstName
MiddleName
LastName
Email

I would need to know how to write an update statement in order to sync with User_name (table1) to FirstName, MiddleName, LastName (table2). Any help would be appreciated.

Dan

dan

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-28 : 22:31:28

first of all I question the need for these two tables. what's the point of Table1 if you have Table2?

Second, you want to update based on what criteria? is there an id in each table you can match up that you aren't telling us about? or are you just inserting from 2 to 1?

www.elsasoft.org

ForceF16
Starting Member

13 Posts

Posted - 2007-02-28 : 22:50:27

I did not include ID here because it is useless on both tables because two tables are not talking to each other. Now, I need to write a trigger update statement in order table1 can update on table2. For example, when I update my name: john a doe (table1: user_name) and it’s automatically updated on table 2 (table2 FirstName, MiddleName, LastName). Hope this helps.

Thanks,
Dan

dan

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-28 : 23:03:08

i see. I would recommend doing this in the sproc, instead of a trigger. supposedly you have a sproc that's updating the values in table2? just have the sproc update table1 as well.

triggers are best avoided if at all possible - I consider them to be like a crutch.

www.elsasoft.org

ForceF16
Starting Member

13 Posts

Posted - 2007-02-28 : 23:27:27

Thanks for your suggestion, but I'd like to know how to write this trigger statement. can you help?

Also, I am a new SQL

Thanks,

Dan

dan

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-01 : 00:40:50

I guess you could do something like this in a AFTER UPDATE trigger:


update t1
set 
   User_name=inserted.FirstName + ' ' + inserted.MiddleName + ' ' + inserted.LastName
   ,User_email=inserted.Email
from 
   Table1 t1 
where
   User_name=deleted.FirstName + ' ' + deleted.MiddleName + ' ' + deleted.LastName
   and User_email=deleted.Email

EDIT: now that I look at this, I don't think it will work. I think you may need to join inserted and deleted on the pk column (whatever that is - you didn't say which is the pk).

suffice it to say, using a proc would be much simpler than using a trigger. I hate triggers simply for this fact: all they do is a side-effect.

www.elsasoft.org

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-01 : 02:42:08

quote:
Originally posted by jezemine

I guess you could do something like this in a AFTER UPDATE trigger:

update t1
set
User_name=inserted.FirstName + ' ' + inserted.MiddleName + ' ' + inserted.LastName
,User_email=inserted.Email
from
Table1 t1
where
User_name=deleted.FirstName + ' ' + deleted.MiddleName + ' ' + deleted.LastName
and User_email=deleted.Email

EDIT: now that I look at this, I don't think it will work. I think you may need to join inserted and deleted on the pk column (whatever that is - you didn't say which is the pk).

suffice it to say, using a proc would be much simpler than using a trigger. I hate triggers simply for this fact: all they do is a side-effect.
www.elsasoft.org

why to use trigger n all this things? if it can be achived through single statement.

hey ForceF16, if ur table didn't have any key its unable to update the table. u must have key id so that it can be.

try this ... i have assumed ur table have userid, so qry would be ...

UPDATE
T2
SET
FULL_NAME = T1.F_NAME + ' ' + T1.M_NAME + ' ' + T1.L_NAME
FROM
TAB2 T2
,TAB1 T1
WHERE
T2.USERID = T1.USERID

let us know, if any issues,

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-01 : 03:03:07

I don't know why but I have strong sensation that all ForceF16 want to do is to know how to write trigger for this, irrespective of whether this can be achieved in more simpler manner or not.

Why don't you just take a look at the BOL to understand how to write triggers, as anyway you are not paying attention to what is suggested here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources