update query question

[djc]

design grid created something like this:

UPDATE tblTable1, tblTable2 SET tblTable1.Field = tblTable2.Field
WHERE (tblTable1.ID = tblTable2.ID) AND (tblTable2.Field Is Not)

There is no FROM clause.

Unfortunately I didn't save it so the following my not be completely
accurate but I had done something similiar to this via sql view:

UPDATE tblTable1 SET tblTable1.Field = tblTable2.Field
FROM tblTable1 INNER JOIN tblTable2 ON tblTable1.ID = tblTable2.ID
WHERE (tblTable1.ID = tblTable2.ID) AND (tblTable2.Field Is Not)

I am confused as to which way is right/wrong? Or if they are both ok which
is better? Or if it depends on the situation, what are the
advantages/disadvantages to both styles?

I just encountered a bunch of stuff at once that I have questions about so I
hope I asked this clearly.

 

[John Vinson]

design grid created something like this:

UPDATE tblTable1, tblTable2 SET tblTable1.Field = tblTable2.Field
WHERE (tblTable1.ID = tblTable2.ID) AND (tblTable2.Field Is Not)

There is no FROM clause.

Correct. An UPDATE clause specifies the tables; you don't use a FROM.

Unfortunately I didn't save it so the following my not be completely
accurate but I had done something similiar to this via sql view:

It certainly isn't accurate. For one thing, you won't be able to
update this because there is no JOIN.

UPDATE tblTable1 SET tblTable1.Field = tblTable2.Field
FROM tblTable1 INNER JOIN tblTable2 ON tblTable1.ID = tblTable2.ID
WHERE (tblTable1.ID = tblTable2.ID) AND (tblTable2.Field Is Not)

This won't work either.

I am confused as to which way is right/wrong? Or if they are both ok which
is better? Or if it depends on the situation, what are the
advantages/disadvantages to both styles?

Since neither of these will work, they're equally good!

One question: what do you mean by "tblTable2.Field Is Not" ? The NOT
operator negates whatever comes after it; i.e. WHERE tblTable2.Field
IS NOT BETWEEN 3 AND 6. The clause as written is meaningless and will
generate an error message. What value of Field do you want to include,
or exclude?

The correct syntax is

UPDATE tblTable1 INNER JOIN tblTable2 ON tblTable1.ID = tblTable2.ID
SET tblTable1.Field = tblTable2.Field
WHERE tblTable2.Field <some valid criteria expression>

 

[djc]

thanks for the reply,

1) "tblTable2.Field Is Not" ? Just forgot the NULL. This was not a
copy/paste, I was just typing away.

2) I don't know where you are refering to when you say 'there is no JOIN'?
The join is in the FROM clause. I may be remembering incorrectly exactly
what I typed originally (was there a FROM or not?? not sure now) but the
root issue and reason for this post is that I did this 2 ways, both of which
worked and returned the same results, and I wanted to know what the
differences, if any, were? advantages/disadvantages/good programming
practice etc...

anyway, I didn't do a very good job of asking the question originally, so I
will wait until it comes up again and ask more clearly,

Thanks anyway.