Update tb1 from #tb1 - 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 2005 Forums

Transact-SQL (2005)

Update tb1 from #tb1

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-01-27 : 00:55:29

I have 2 tables with same no of columns and same datatype

table 1

create table tb1
(col1 int, col2 int , col3 varchar(10),col4 varchar (10), col5 varchar (10))

table 2

create table #tb1
(col1 int, col2 int , col3 varchar(10),col4 varchar (10), col5 varchar (10))

tb1 contains original data and #tb1 contains modified data if have any

I want to compare rows of both the tables and if any column have no match then I want to
update tb1 with #tb1 dat.

plz tell me how I can achieve this ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 01:01:54

select * from tbl1 intercept select * from #tbl1 --- u will get the same records which are matching

select * from tbl1 except select * from #tbl1 -- u will get the not matched records then check the id and updated the requiered table

abc123
Starting Member

47 Posts

Posted - 2009-01-27 : 01:04:29

suppose there is only change in one column say col3 , then how i can find ount col3

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-27 : 01:06:31

try this once,
select col3 from tbl1 except select col3 from #tbl1

abc123
Starting Member

47 Posts

Posted - 2009-01-27 : 01:11:53

ok..one more query
if my front end is returning 5 rows.how I can check for each row?
tb1 have 5 rwos and #tb1 also have 5 rows with updated data

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 01:26:55

how can u check the front end output with the table data

check with ur output select query if ur using except r intercept u should use the same no of columns in columns both queries

abc123
Starting Member

47 Posts

Posted - 2009-01-27 : 01:38:56

front end inserted rows in table #tb1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 01:51:09

then use except u will get the differ rows

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-28 : 05:15:38

quote:
Originally posted by bklr

select * from tbl1 intercept select * from #tbl1 --- u will get the same records which are matching

select * from tbl1 except select * from #tbl1 -- u will get the not matched records then check the id and updated the requiered table

its INTERSECT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 05:17:27

quote:
Originally posted by lionofdezert

quote:
Originally posted by bklr

select * from tbl1 intercept select * from #tbl1 --- u will get the same records which are matching

select * from tbl1 except select * from #tbl1 -- u will get the not matched records then check the id and updated the requiered table

its INTERSECT

nice catch
just typing mistake yaar

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-28 : 05:20:51

;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources