hi everyone
i have reseached this topic and cannot seem to find a solution that suits my problem, please help!!
I have two tables, "Inventory" which contains 30,000 or so records, with about 2000 duplicates. Each duplicate contains the exact same data in every field except one, Field "Code", which may have "Agreed" or "Rejected".
The second table, "Duplicate Coding" contains the correct field value for "Code" for the records which are duplicates. This table was created by using Find Duplicates and then running a query to negate the incorrect Code values.
Now I need to merge the data, I wish to delelte all duplicate lines where the Code value from Duplicate Coding does not match, WITHOUT affecting the other lines in table Inventory.
The two tables both contain an ID number to reference records.
I hope this makes sense? I know there will be a simple solution but I can't seem to make it work!
Thanks in advance!
8  3866  ADezii 8,834
Recognized Expert Expert
Kindly post some data to visually illustrate your request.
-
update tblInventory
-
set tblInventory.Code = tblDuplicates.Code
-
where tblInventory.ID = tblDuplicates.ID
NeoPa 32,579
Recognized Expert Moderator MVP
Try the following query. Let me know if it complains about using a non-updatable query to delete. - DELETE
-
FROM tblInventory AS tI INNER JOIN
-
[Duplicate Coding] AS tDC
-
ON tI.ID=tDC.ID
-
AND tI.Code<>tDC.Code
NeoPa 32,579
Recognized Expert Moderator MVP
PS. Please remember to title your questions correctly in future ;)
Thank you everyone :)
I ended up using this format: - DELETE
-
FROM tblInventory AS tI INNER JOIN
-
[Duplicate Coding] AS tDC
-
ON tI.ID=tDC.ID
-
AND tI.Code<>tDC.Code
with a small change, as this exact query timed out every time I tried to run it. Thank you NeoPa for helping me fix my shoddy data!!
Cheers
Nat
NeoPa 32,579
Recognized Expert Moderator MVP
No worries Nat.
I'd be interested to see exactly what SQL code you did use mind you, to see where in my code I could have done it differently perhaps.
:S
So very sorry I should have stated that correctly, changes were only due to naming conventions, not coding issues!
thanks for your help (again!)
NeoPa 32,579
Recognized Expert Moderator MVP
You're sorted now Nat, that's the important thing :)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Wm |
last post by:
I have a table of users in mySQL that appears to have a lot of duplicates.
What's the best way to look at the userID and email and delete the
duplicates?
Thanx,
Wm
|
by: Marlene |
last post by:
Hi All
I have the following scenario, where I have found all the duplicates
in a table, based on an order number and a part number (item).I might
have something like this:
Order PODate Rec Qty Invoice# Item
Supplier Status POReceivedDate
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
|
by: AK |
last post by:
Hi
Our product uses MS-SQL Server 2000. One of our customer has 10
installations with each installation stroring data in its own database.
Now the customer wants to consolidate these databases into one and we
already have plan for that by consolidating one DB at a time. But first
they want to find how many unique or duplicate entries they have across
all the 10 databases
Assumptions:
|
by: yin_n_yang74 |
last post by:
I am new to SQL and SQL Server world. There must be a simple solution
to this, but I'm not seeing it. I am trying to create a crystal
report (v8.5) using a stored procedure from SQL Server (v2000) in
order to report from two databases and to enable parameters.
When I create the stored procedure, it joins multiple one-to-many
relationship tables. This results in repeated/duplicate records. Is
this an issue that should be solved within...
|
by: mGracz |
last post by:
Welcome,
how can I alter following table in order to reduce neighbouring
duplicates (symbol, position, quantity, price).
Nr Symbol Position Quantity
Price Date
1. wz9999b 1 1.0
2500.0 2007-05-09 08:09:42.653
2. wz9999b 2 12.0
| | |
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for
renaming the duplicate records? My thinking was to take the results of the
duplicate query, and somehow have it number each line where there is a
duplicate (tried a groups query, but "count" won't work), then do an update
query to change the duplicate to...
|
by: ryan.paquette |
last post by:
In the table there are 2 fields in which I wish to limit (i.e. No
Duplicates)
Although I do not want to limit them to "No Duplicates" separately.
I need them to be limited to "No Duplicates" as if they were one
field.
The 2 fields are "Employee_Name" & "Training_Course".
*(There is another table for Employees & another for Training Courses,
both of which are related to this table.)
|
by: Bishopkris40 |
last post by:
Please help.
I am working on a database that logs the alarm events from a machine. The problem I have is that I need to explain why the machine has stopped and calculate the time it stops for. I then report the total time stopped.
This is fine when only 1 alarm was present as the raw table only has 1 relevant entry at that time. But if there were two alarms then I get two entries at that time. I only need one of the entries at that time,...
|
by: john.cole |
last post by:
I have searched all the groups I can, and I still haven't been able to
come up the solution I need. I have the following problem.
In my form named sbfrmSpoolList, I am entering a job, spool and
revision number. My table is indexed properly to not allow
duplicates, but I would like teh user to be notified that they are
typing a duplicate via a message box, then I woulld the update of the
record to be cancelled. I have tried the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
