Updating a coulme based on info form 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

SQL Server 2000 Forums

SQL Server Development (2000)

Updating a coulme based on info form another table

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-04-11 : 19:24:59

I am trying to write a script that will update a certain table in a column with the column’s current number + a number from a column in a different table. He is kind the lay out of my database:
I have a table named PublicINFO with a column named Points (which has a value of 0 to start), now there is another table called PrivetINFO with a column named PointsPerDay. I want to take the "0" in the Points column and then add it to the number in the PoitnsPerDay column and pernamently change the Points column. So if Points=0 and PointsPerDay=10 then after the script is ran I want points to equal 10. And then if the script is ran again have it equal 20. Any suggestions would be helpful (and if possible please include an example) Thanks!

motokevin
Starting Member

36 Posts

Posted - 2002-04-11 : 19:51:17

This should do it dude.

Update PublicINFO Set
PublicINFO.Points = PublicINFO.Points + PrivateInfo.PointsPerDay
Where PublicINFO.<KeyColumn>=PrivateINFO.<KeyColumn>

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-04-11 : 20:04:19

Now what do you mean by "Where PublicINFO.<KeyColumn>=PrivateINFO.<KeyColumn>" is that a command I am suposed to put in?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-11 : 20:07:50

Hi

Have a read of this

www.sqlteam.com/item.asp?itemId=3876

It should answer a bunch of questions

Damian

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-11 : 20:31:27

If you post the exact table structure of PublicINFO and PrivateINFO I'm sure we could give you and exact SQL Statement to run.

When you join PublicInfo to PrivateInfo there needs to be a relationship between to the two so that when you are updating PublicInfo the code will know which record to get from PrivateInfo.

Say you had UserID columns in both PublicInfo and PrivateInfo, and you wanted to do your point manipulation on UserID of 289 then the following would do what you wanted

UPDATE pu
SET pu.Points = pu.Points + prv.Points
FROM PublicINFO pu 
INNER JOIN PrivateINFO prv
     ON (pu.UserID = prv.UserID)
WHERE pu.UserID = 289

You should still read up on the article that Merkin mentioned. It will explain everything in more detail.

Got SQL?

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-04-11 : 20:42:51

Ok, I don't see what I am doing wrong here. I use this code:
Update PublicINFO Set
PublicINFO.Points = PublicINFO.Points + PrivateInfo.PointsPerDay
Where PublicINFO.Points=PrivateINFO.PointsPerDay

And get this error:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'PrivateInfo' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'PrivateINFO' does not match with a table name or alias name used in the query.

And I am still not sure what is ment by "Where PublicINFO.<KeyColumn>=PrivateINFO.<KeyColumn>"

motokevin
Starting Member

36 Posts

Posted - 2002-04-11 : 20:49:49

Sorry Eagle,

I forgot a line in there.

Here's the complete query:

Update PublicINFO Set
PublicINFO.Points = PublicINFO.Points + PrivateInfo.PointsPerDay
From PublicINFO,PrivateINFO
Where PublicINFO.<KeyColumn>=PrivateINFO.<KeyColumn>

that should eliminate the errors.

quote:
Now what do you mean by "Where PublicINFO.<KeyColumn>=PrivateINFO.<KeyColumn>" is that a command I am suposed to put in?

I am assuming that the two tables, PublicINFO and PrivateINFO, have a column in common. In other words, the have a column that they can be JOIN-ed on.

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-04-11 : 21:05:07

Thanks for all your help guys.
Motokevin, The From command has fixed the errors and is now doing what I want. I thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources