By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Update SQL 2000 Query (converting an Old Access 2k query to SQL)

P: n/a
Hello, I have the following query in Access 2000 that I need to convert
to SQL 2000:

UPDATE tblShoes, tblBoxes
SET tblShoes.Laces1 = Null
WHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND
((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))
WITH OWNERACCESS OPTION;
The ShoesID in the tblShoes table is an autonumber, however the records
in the tblBoxes have the ShoesID converted to text.

This query runs ok in Access, but when I try to run it in the SQL
Server 2000 Query Analizer I get errors because of the comma in the
"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoes
field named Laces1 to NULL for every record matching the ones in the
tblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8
field.

Any help would be greatly appreciated.

JR

Mar 30 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
IL***@NETZERO.NET wrote:
Hello, I have the following query in Access 2000 that I need to convert
to SQL 2000:

UPDATE tblShoes, tblBoxes
SET tblShoes.Laces1 = Null
WHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND
((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))
WITH OWNERACCESS OPTION;
The ShoesID in the tblShoes table is an autonumber, however the records
in the tblBoxes have the ShoesID converted to text.


Try:

UPDATE tblShoes
SET laces1 = NULL
WHERE EXISTS
(SELECT *
FROM tblboxes AS B
WHERE CAST(B.shoesid AS INT) = tblShoes.shoesid
AND B.code8 IN ('A','B')) ;

(untested)

You can find the full syntax and examples of the UPDATE statement in
SQL Server Books Online.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Mar 30 '06 #2

P: n/a
Hello David, the code does works. I tested it and it does set to NULL
those fields. Thanks.

Mar 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.