Updating data from another table

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

General SQL Server Forums

New to SQL Server Programming

Updating data from another table

Author

Topic

V.V.
Starting Member

32 Posts

Posted - 2011-09-29 : 04:33:16

Hi guys!

I have two tables: V1.dbo.Clients and V2.dbo.Clients. The tables are identically. At some point, my colleagues imported a .csv file in V1 but the file wasn't fully populated with data (only specific data that were needed for a report). After a while, I imported myself this file with all data in V2. Ok, basically I have same number of rows in both databases but not all columns are identically. My job is to do this.

From V1 I want to update a number of 800 rows which control key is made from two columns: cnp & contract.

How can I update from V2 specific columns for these 800 rows?

The easy way is to make a full update on aproximative 20.000 rows which are identified in both databases through a column, DataValidation, but I don't want this. The script would be like this:

update v1
set v1.street=v2.street
from V1.dbo.Clients v1, V2.dbo.Clients v2
where v1.cnp=v2.cnp and v1.contract=v2.contract and v1.DataValidation='20110101'

That would be ok, but I want to update just for the 800 rows that I mentioned. How can I write the scrip above to update data only for those 800 rows?

If I change the script and put like this:

update v1
set v1.street=v2.street
from V1.dbo.Clients v1, V2.dbo.Clients v2
where v1.cnp=v2.cnp and v1.cnp in
(
cnp1,
cnp2,
...
)
and v1.contract=v2.contract and v1.contract in
(
contract1,
contract2,
...
)

I just try it on a test database and seems to work.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-09-29 : 04:54:19

Hi,

You can use MERGE STATEMENT in this case.
Refer: http://technet.microsoft.com/en-us/library/bb510625.aspx

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

V.V.
Starting Member

32 Posts

Posted - 2011-09-29 : 05:04:12

Isn't there a simple solution?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-29 : 05:28:35

Do you have a SELECT to identify/get this 800 rows?

No, you're never too old to Yak'n'Roll if you're too young to die.

V.V.
Starting Member

32 Posts

Posted - 2011-09-29 : 05:47:29

webfred, I can select them by this combined key: cnp+contract (two distinct columns).

One cnp can have more than one contract so that's why I use this key.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-29 : 08:50:08

But where do you have the values for that combined key.
In other words how will the SELECT statement look if you only want to retreive that 800 rows?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources