Posted by David Portas on 03/30/06 22:30

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
--

• Next in forum: Re: Can this be optimized? Newbie question
• Prev in forum: Re: Bind Variable in CURSOR
• Thread view: Re: Update SQL 2000 Query (converting an Old Access 2k query to SQL)

[Reply to this message]