Update with criteria and value 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

Update with criteria and value from another table

Author

Topic

Inno
Starting Member

33 Posts

Posted - 2006-05-16 : 10:58:41

Hi, I have a problem which is probably really simple to solve but I have gotten a tilt and cant get it...Hoping you can help me.
What Im trying to do in pseudo-code:

Get A from a tableX where B = 0

Check tableY for each A and get B from there

Update all B in tableX where TableX.LotNo=TableY.Lotno with the value from TableY.B

Anyone feel generous enuf to help a newbie out?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-16 : 11:17:48

1. SELECT A FROM tableX WHERE B = 0
2. SELECT y.B FROM tableY y INNER JOIN tableX x WHERE y.A = x.A AND x.B = 0
3. UPDATE x SET x.B = Y.B FROM tableX x INNER JOIN tableY y ON x.LotNo = y.Lotno

That's taking you quite literally, so I'm not sure if it's exactly what you wanted.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-16 : 11:39:29

quote:
Originally posted by derrickleggett

UPDATE x SET x.B = Y.B FROM tableX x INNER JOIN tableY y ON x.LotNo = y.Lotno

MeanOldDBA,
I'll modify ur answer to suite the question (as I understand)

UPDATE x SET x.B = Y.B FROM tableX x INNER JOIN tableY y ON x.A= y.A where X.B= 0

Srinika

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-16 : 11:48:21

How does that address the tableX.LotNo = tableY.LotNo?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 12:43:58

I guess the question isn't clear (particularly on how the 2 tables should be joined).

Inno, can you give us an example (preferably in the form below - change this to how some of your data really looks, and give us the result you'd like for that sample data)...

"--data
declare @tableX table (A int, B int, LotNo int)
insert @tableX
          select 1, 0, 4
union all select 2, 0, 5
union all select 3, 1, 6

declare @tableY table (A int, B int, LotNo int)
insert @tableY
          select 1, 7, 4
union all select 2, 8, 9
union all select 3, 9, 6

--results I'd like in @tableX
          select 1, 7, 4
union all select 2, 0, 5
union all select 3, 1, 6"

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Inno
Starting Member

33 Posts

Posted - 2006-05-16 : 16:01:24

Yeah maybe I wasnt clear enough. I dont really understand the scheme above so I'll try to make it clearer.

TableX (LotNo int, Price float...)

TableY (LotNo int, Price float...)

My problem: Where Price is 0 in TableX I need to get Price from TableY and update TableX where TableX.LotNo = TableY.LotNo.

There is of course more columns in each table and they differ.

Cheers for having patients with a newbie.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-16 : 16:10:53

May be this :

UPDATE x SET x.Price  = Y.Price  
FROM tableX x INNER JOIN tableY y ON x.LotNo = y.LotNo  
where x.Price = 0

--------------------------------------
MeanOldDBA,
I just assumed that

quote:
Originally posted by Inno

Check tableY for each A and get B from there

means ==> x.A = y.A

Srinika

Inno
Starting Member

33 Posts

Posted - 2006-05-16 : 16:37:54

Seems like just what I was looking for, Ill have to wait to try it until I get to my own computer, but big thanx! Guess I have to learn up on inner joins...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-16 : 16:53:30

U can learn a lot about Inner Join (and other joining) + Update + lots of stuff, if u refer the Books Online (SQL server Help) and do experiment and visit this site quite frequently, go thru frequently asked Qs ....

Srinika

Inno
Starting Member

33 Posts

Posted - 2006-05-16 : 17:28:14

I will most definately. Just found this site today so I'll spend alot of time here from now on :)

Inno
Starting Member

33 Posts

Posted - 2006-05-17 : 04:04:20

Worked fine Srinika. Thnx alot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources