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)
 compare tables, test for condition

Author  Topic 

Kristin
Starting Member

16 Posts
Posted - 2004-08-09 : 18:22:49
Is it possible, rather, can someone explain to me how.... to test each record in a table for a condition?

For instance, I need to run through each record in a table and perform a query on it. This query will update another table.

I have a table Chains and a table Records in my database MROresults.

I need to run the update query (to Records) for each record in chains....

UPDATE records
SET chainpath = ?
WHERE (speciminid = ?)

Any ideas? Am I way out of the ballpark here? Is this possible? IT is sort of like I am using each record in one table as the parameters for my update query on another table.

Terrible confused with this issue... I know you can't perform an update query on two tables, but if I query one table, line by line, aren't I getting around it?

Any input or thoughts would be appreciated.



kristin

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-08-09 : 18:31:36
You need to use a join in the update statement. Example:

UPDATE t1
SET t1.ColumnC = t2.ColumnC
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Column1 = t2.Column1

Tara
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-08-09 : 18:33:13
Not sure if this will be useful but it's worth a shot:

UPDATE records
SET a.chainpath = b.chainpath
FROM records a inner join sometable b
ON a.speciminid = b.speciminid


--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-08-09 : 18:34:15
ugh...I'm obviously a slow typer...
Go to Top of Page

Kristin
Starting Member

16 Posts
Posted - 2004-08-10 : 10:05:37
hmmm:) ok, but how do I check each record in the table? Do I use a FOR EACH....NEXT statement and run the query? I need to check first if table1.field1 = table2.field1 (they each have the field SpecimenID) then if this is true, execute the update statement. My main problem is checking each record in the first table...Do I use some Do while loop until EOF? I'm VERY new to all this coding. Most of what I have done so far is through SQL builder. Haven't written too much code yet...

thanks guys for all the input! Every little bit helps! Waiting to hear what you all have to say:)
kristin
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-08-10 : 10:18:39
The queries tara and I have posted will do exactly that, only it does them all at once! This is what makes SQL so great...it is a so called "set-based" language meaning that it can do work on entire sets of data instead of only row-by-row which most application-programmers are used to. Try the solution and see if it works for you, but be sure to do it in a testing environment...
Go to Top of Page

Kristin
Starting Member

16 Posts
Posted - 2004-08-10 : 10:39:39
ah hah:) ok, going to try it now, thanks for clearing that up for me. I guess I'm trying to make this harder than it is??
Go to Top of Page

Kristin
Starting Member

16 Posts
Posted - 2004-08-10 : 13:13:44
OK! Here is what I have..
Private Sub Command1_Click()

Update records
Set records.chainpath = chains.chainnum
FROM records INNER JOIN
chains ON records.speciminid = chains.speciminid

End Sub

it doesn't like "FROM....."
it is red...says expected expression and highlights ON.

any ideas where I went wrong?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-08-10 : 13:22:03
[code]
Private Sub Command1_Click()

UPDATE r
SET r.chainpath = c.chainnum
FROM
records r
INNER JOIN chains c ON r.speciminid = c.speciminid

End Sub
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristin
Starting Member

16 Posts
Posted - 2004-08-10 : 15:26:05
MeanOldDBA!!

ok:) maybe I am confused....what are you calling r and c?? are those the names of the tables? Because chainpath, chainnum and speciminid are my fields... My tables are records and chains...
Name of my database is MROresults.

I am missing something.....
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-08-10 : 15:30:46
r and c are aliases. So r.speciminid is the same as records.speciminid. You put the alias after the table name so you don't have to keep typing the entire table name. Did you try his code? The alias is required for an UPDATE with a JOIN.

Tara
Go to Top of Page

Kristin
Starting Member

16 Posts
Posted - 2004-08-10 : 15:43:23
Yeah, just ran it, what do I define r as then?
kristin:)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-08-10 : 15:45:08
I don't understand the question. r is an alias to Records.

Tara
Go to Top of Page

Kristin
Starting Member

16 Posts
Posted - 2004-08-10 : 15:52:30
When I type the code in as is....it tells me variable not defined... the r. then c.

do I have to Dim r as something?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-08-10 : 15:54:54
I'm not a programmer, but I believe the entire UPDATE string needs to be put into a variable. Then you execute the variable.

Tara
Go to Top of Page

Kristin
Starting Member

16 Posts
Posted - 2004-08-10 : 16:03:49
really? I thought if I opened a connection to the database, I could execute this command on it. I am just not good at declaring variables and working with vb code. What I tend to do is write everything in SQL, then call the command on an as needed basis. So this is a bit different for me.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-08-10 : 18:03:27
It seems you're not getting the concept of the difference between vb and sql. You cannot run SQL directly from your vb-code without first creating a db-connection, adding the sql to a variable and the execute the sql against the database. So this:

Private Sub Command1_Click()

UPDATE r
SET r.chainpath = c.chainnum
FROM
records r
INNER JOIN chains c ON r.speciminid = c.speciminid

End Sub

is just a way of shortening things down for explanation purposes. I'm *pretty* sure Derrick didn't mean for you to use this exact code. I'm no vb-guy but something like this is close to what I would do in vbscript (asp):

Private Sub Command1_Click()

Dim SQL as String
SQL = "UPDATE r " & _
"SET r.chainpath = c.chainnum" & _
"FROM " & _
"records r" & _
"INNER JOIN chains c ON r.speciminid = c.speciminid"
'Create database connection called "Conn"
Conn.Execute(SQL)

End Sub

If this is only to be done once it would be alot better to use Query Analyzer.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-08-10 : 19:01:08
And if it's done a lot, you should make a stored procedure and call it from VB. Sorry about that earlier. I had no idea what you were trying to do. I just knew what was wrong with the update statement. :) I should pay more attention.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site