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.Sho esID)=Int([tblBoxes].[ShoesID])) AND
((tblBoxes.Code 8)="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 2 3634 IL***@NETZERO.N ET 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.Sho esID)=Int([tblBoxes].[ShoesID])) AND ((tblBoxes.Code 8)="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.shoesi d
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
--
Hello David, the code does works. I tested it and it does set to NULL
those fields. Thanks. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: jason |
last post by:
Hi Ray...a while ago you explained an elegant solution to enable me to
CREATE and EDIT existing tables and queries inside my online access 2000
database.... could you provide refresher links on this or possibly some
starter code?
I am just so sick of the syncronization problems that come into play when
you adjust underlying tables and queries offline and then have to overwrite
the live database?
Many thanks Jason
|
by: wiredog |
last post by:
I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping some one can give me some
guidance. I believe I have the first portion of the query correct but
do believe this requires a "NESTED" argument. This is where I am
lost.
My Original MS ACCESS Query reads--
SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid(,5,2)=,
|
by: Karen Middleton |
last post by:
In MS Access I can do in one SQL statement a update if exists else a
insert.
Assuming my source staging table is called - SOURCE and my target
table is called - DEST and both of them have the same structure as
follows
Keycolumns
==========
Material
|
by: Phil Powell |
last post by:
<cfquery name="getAll" datasource="#request.dsn#">
SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn,
U.choiceId, U.experience, T.label AS teamLabel, R.label AS
roleLabel
FROM User U LEFT JOIN UserTeamAssoc UTA
ON UTA.userID = U.userID,
Role R, UserRoleAssoc URA, Team T
WHERE U.userID = URA.userID
AND URA.roleID = R.roleID
AND U.userId > 1
|
by: Miguelito Bain |
last post by:
hi everybody-
i've got a conundrum...
i inherited some old databases, and i'm trying to convert them. i run
office xp with access 2002, and all of the databases i manage are
either in 97 format or 2000 format.
i have a list box that works in access 97 but not 2000. here's my
situation...
| |
by: Stefan V. |
last post by:
Hello!
I am trying to convert a query written for SQL Server 2000 database
tables, to a MS Access query.
Here is what I have in SQL Server:
SELECT t2.*,
CASE WHEN t2.QType = '3' THEN t1.Note ELSE CASE WHEN t2.QType = '2'
THEN
CASE WHEN CONVERT(varchar(100), t1.ANumber) = '1' THEN 'Yes' ELSE 'No'
END ELSE CASE WHEN CONVERT(varchar(5), t2.Qnumber)+'.' +
|
by: deko |
last post by:
I've tried each of the below, but no luck.
UPDATE tblEntity As tbl INNER JOIN search3220 As qry ON tbl.Entity_ID =
qry.Entity_ID SET tbl.Cat_ID = 289;
UPDATE tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
seach3220.Entity_ID SET tblEntity.Cat_ID = 289;
The only issue I can imagine aside from syntax is that Entity_ID is not
unique in the query. tblEntity will only have one Entity_ID of 1700, for
|
by: |
last post by:
Hi,
I have a major problem. I use Windows 2000 highest SP and ACCESS 2000 inside
Office 2000 SP-3.
I want to use the following simple command inside ACCESS in VBA
UPDATE tbl_SATURN_Benutzer SET bool_IstDatensatzAktuell = False,
bool_ImportCheckFlag = True, dat_Löschdatum = #01/25/2005 18:38:17# WHERE
((bool_ImportCheckFlag = False) AND (dat_Löschdatum = Null) AND
|
by: Benoit Martin |
last post by:
I'm working on a project in VB.net connecting to a SQL Server 2000 database
that I can't modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update
sub, I get an error.
The problem seems to be that one of the fields was named 'desc' which is a
reserved word for SQL.
I've been able to work around this design flaw 'till now but it looks like
the update sub can't...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |