Probably driving you crazy? Update Problem

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

Other Forums

MS Access

Probably driving you crazy? Update Problem

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-24 : 11:06:13

The following update does not return any errors, even goes as far as warning me that one record will be updated but the fields remain unchanged?

UPDATE tblHollowcoreSchedule SET Load = 1 AND ShippingDate = #10/23/2003#
WHERE (Barcode IN
(SELECT TOP 1 Barcode FROM tblHollowcoreSchedule
WHERE Project = '10002'
AND Component = '401'
AND Load = 0
ORDER BY Barcode ASC));

If I just use the select, a record as expected is returned. Any ideas?

Mike B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-24 : 12:41:34

Does this SELECT statement return the right record?


SELECT *
FROM tblHollowcoreSchedule t1
INNER JOIN (SELECT TOP 1 Barcode FROM tblHollowcoreSchedule
	WHERE Project = '10002'
	AND Component = '401'
	AND Load = 0
	ORDER BY Barcode ASC) t2 ON t1.Barcode = t2.Barcode

Tara

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-24 : 13:29:25

How many barcodes are returned with this query:

SELECT	DISTINCT Barcode 
FROM	tblHollowcoreSchedule
WHERE 	Project = '10002'
AND 	Component = '401'
AND 	Load = 0

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-24 : 15:12:05

Suppose incorrect syntax in this:

SET Load = 1 AND ShippingDate = #10/23/2003#

Should be Comma instead.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-24 : 15:16:27

He should get a syntax error though if that were the case (he said that he didn't get an error but the data didn't change). Access must allow AND in the SET part of the UPDATE statement.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-24 : 15:30:34

Yes,
Access allows AND in that but in this
case the UPDATE is too much for 'him'...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-24 : 15:34:24

the effect of:

SET Load = 1 AND ShippingDate = #10/23/2003#

is this:

SET Load = (1 AND ShippingDate = #10/23/2003#)

which means that the expression "(1 AND ShippingDate = #10/23/2003#
)" is evaluated, which comes to True or False (or -1 or 0), and Load is set equal to the result.

Since "1 and {anything}" evaluates to {anything}, the end result is:

SET Load = (ShippingDate = #10/23/2003#)

which says "Set load equal to -1 if the shipping date is #10/23/200# or 0 if it is not".

can be a good trick and you can write some neat code, but I doubt this is what he is trying for in this case.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-24 : 18:07:29

Exactly, Jeff!!
=======================
One more interesting trick of Access97.
As you all know this is not allowed:
select * from (select * from t where ...) as tt
Now try this:
select * from [select * from t where ...]. as tt

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-24 : 19:12:24

Thanks for the help guys/girls. Really appreciate it. I got extremely busy after I posted this, as well as found the error Stoad has found. "TYPOS/BRAIN MISFIRES", have to hate them.

Not really sure how to read the comment:

"Access allows AND in that but in this case the UPDATE is too much for 'him'...".

Thanks all for your replies!

Mike B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-24 : 19:15:25

I think he is just saying that AND is not what you wanted in your query.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources