Microsoft SQL Server forum - Update SQL 2000 Query (converting an Old Access 2k query to SQL)

Author

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

ILCSP@NETZERO.NET

2006-03-30, 11:28 am

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

David Portas

2006-03-30, 8:27 pm

ILCSP@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/ms130214(en-US,SQL.90).aspx
--

ILCSP@NETZERO.NET

2006-03-30, 8:27 pm

Hello David, the code does works. I tested it and it does set to NULL
those fields. Thanks.

Sponsored Links