SQL - 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

SQL

Author

Topic

belkin_99
Starting Member

27 Posts

Posted - 2008-09-14 : 01:43:08

I have Two Tables:
X with Index, Name, and Grade fields.
Y with Index and Grade.
Table X has 30 Records Index (1-30 randomly), Name (A..Z), and Grade (all Null)
Table y has 5 Records Index (4,5,6,20,and 30), and Grade (level1,level2,level3,level4, and level5)

I want to assign the levels in Y.Grade into X.Grade based on
X.Index = Y.Index
while for X.Index <> Y.Index the X.Grade should = 'None'
Please, How can I do that without change the total number of Record for Table X

Thanks in Advance.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-14 : 08:45:07

Sound like you want 2 update statements. One that updates X based on the values in Y (hint: the from clause of the update statement will contain both tables), and then one to set the grades to None if they are still null.

Is this homework?

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-14 : 09:19:57

i think op needs only a single update statement.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-14 : 10:32:10

True, with a left join.
It's clearer with two, if it is a homework problem. More efficient with just one if it's a real-life system

--
Gail Shaw
SQL Server MVP

belkin_99
Starting Member

27 Posts

Posted - 2008-09-14 : 10:44:53

I really appreciate your response,
I need to put it in one statement instead of two.

Update X (Grade)
left join Y on X.Index = Y.Index
And X.Grade = Y.Grade
Where X.Grade IS NULL.

Please Is this will WORK??!
If Not what is the optimize method.
Again I appreciate your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-14 : 12:09:59

sounds like this is what you want:

update x set
x.grade = isNull(y.grade, 'none')
from x
left join y on y.index = x.index

Be One with the Optimizer
TG

belkin_99
Starting Member

27 Posts

Posted - 2008-09-14 : 12:44:21

Thanks TG,
But Is this will work??
Because the number of records in Y (only 5 records) is less than the number of X (30 records)mismatch in the numbers of records in both tables.
And some X.Index number is not in Y.Index

Please I would like to verify about these points

Thank A lot,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-14 : 13:48:56

quote:
Originally posted by belkin_99

Thanks TG,
But Is this will work??
Because the number of records in Y (only 5 records) is less than the number of X (30 records)mismatch in the numbers of records in both tables.
And some X.Index number is not in Y.Index

Please I would like to verify about these points

Thank A lot,

have you had a look at Books Online about LEFT JOIN? Have a look at description of left join and see if it fits your requirement. If you have any doubt ask and we will help you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-14 : 13:49:49

quote:
Originally posted by TG

sounds like this is what you want:

update x set
x.grade = isNull(y.grade, 'none')
from x
left join y on y.index = x.index

Be One with the Optimizer
TG

I would prefer to use coalesce instead of isnull

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-14 : 17:11:53

quote:
Originally posted by belkin_99

Thanks TG,
But Is this will work??
Because the number of records in Y (only 5 records) is less than the number of X (30 records)mismatch in the numbers of records in both tables.
And some X.Index number is not in Y.Index

Please I would like to verify about these points

Thank A lot,

You can always see for yourself with a test. In fact whenever you are about to perform an UPDATE it is a good idea to either:
change the UPDATE to a SELECT so you can see all the rows you would change when it is an UPDATE.
or
do a BEGIN TRAN before the update. If you don't like the results then do a ROLLBACK. If you are happy with the results do a COMMIT.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-14 : 17:30:01

quote:
Originally posted by visakh16I would prefer to use coalesce instead of isnull

Are you going to share your reasoning? :)

-ansi standard?
-the datatype promotion of subsequent arguments reason?
-performance?

I honestly don't have a strong opinion on it - as long as the developer understands these differences and gets the "their" correct results, I'm happy.

btw - interesting article (which is sort of a response Mladen's speed test between coalesce and isnull)
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx

Be One with the Optimizer
TG

belkin_99
Starting Member

27 Posts

Posted - 2008-09-15 : 22:43:23

First of All Thank you for your help.
Thats worked perfectly.

Again Thanks for all who reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources