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

General SQL Server Forums

New to SQL Server Programming

Update where

Author

Topic

paull
Yak Posting Veteran

50 Posts

Posted - 2009-04-07 : 11:34:46

Hi guys,
I am trying to work out how to do an update from one table to another and I am going round in circles!!
It is probably a very basic question, but...

I have a feeder table called Master and a number of Sample tables (Sample1, Sample2 etc).
I need to update certain columns in Sample(n) from Master when the column in Master is Not Null.

MASTER
ID Data1 Data2 Data3
123 Orange Banana BLANK
456 Red BLANK Apple

I would like to update Sample1.Data1 with "Orange"; Sample1.Data2 with Banana when there is a match on the ID 123.

For ID 456 it would update Data1 with "Red", Data3 with "Apple" and leave whatever is in Data2

I really hope that make sense!!!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:37:45

simple update?


update s
set s.data1=m.data1,
s.data2=m.data2,
s.data3=m.data3
from sample1 s
join master m
on m.id=s.id

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-07 : 11:43:42

quote:

Originally posted by visakh16

simple update?


update s
set s.data1=ISNULL(m.data1,s.data1),
s.data2=ISNULL(m.data2,s.data2),
s.data3=ISNULL(m.data3,s.data3)
from sample1 s
join master m
on m.id=s.id

just changed it for null values in master.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:45:42

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

simple update?

update s
set s.data1=ISNULL(NULLIF(m.data1,''),s.data1),
s.data2=ISNULL(NULLIF(m.data2,''),s.data2),
s.data3=ISNULL(NULLIF(m.data3,''),s.data3)
from sample1 s
join master m
on m.id=s.id

just changed it for null values in master.

and if its blank

paull
Yak Posting Veteran

50 Posts

Posted - 2009-04-07 : 12:06:54

Thank you so much guys! Can I ask though, why do I need to repeat the destination column?
i.e set s.data1=ISNULL(m.data1,s.data1). Why couldn't it just be ..

set s.data1=ISNULL(m.data1),

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-07 : 12:21:14

Because ISNULL replaces a NULL with a value Therefor if m.data1 was NULL then the value from s.data1 would be used.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

paull
Yak Posting Veteran

50 Posts

Posted - 2009-04-07 : 12:40:46

Of course! Sorry, I was being a bit stupid there!!! Many thanks again!

paull
Yak Posting Veteran

50 Posts

Posted - 2009-04-08 : 09:57:14

Hi again, if I wanted to produce a report of the changes made, something like a pivot table, where would I start to go about it? For example, if my previous example had a column called Orig_Data1 and the entry for ID 123 was "Oragne",and my query amended Data1 to Read "Orange", it would be good to show the powers that be that x number of instances occurred where "Orig_data1"=Oragne and Data1=Orange.

I really hope that makes sense!!
I suspect that this is something to undertake after a bit more use of the basics and maybe I should just export to excel and run a pivot there, but it would be nice to run the update and have the report generated at the same time.
Many thanks (again) for your help and patience!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-09 : 04:46:03

Pivoting or CROSS TAB are best done outside the database.

If it's an ad hoc Thing I'd recommend just producing a nice normalised dataset from sql and then dump that into excel or similar and then pivot there.

You *can* do pivot in sql. If you need to...

do a search for CROSS TAB in this site. you'll find a ton of links.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:20:20

quote:
Originally posted by paull

Hi again, if I wanted to produce a report of the changes made, something like a pivot table, where would I start to go about it? For example, if my previous example had a column called Orig_Data1 and the entry for ID 123 was "Oragne",and my query amended Data1 to Read "Orange", it would be good to show the powers that be that x number of instances occurred where "Orig_data1"=Oragne and Data1=Orange.

I really hope that makes sense!!
I suspect that this is something to undertake after a bit more use of the basics and maybe I should just export to excel and run a pivot there, but it would be nice to run the update and have the report generated at the same time.
Many thanks (again) for your help and patience!

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources