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 3835
Kindly post some data to visually illustrate your request.
-
update tblInventory
-
set tblInventory.Code = tblDuplicates.Code
-
where tblInventory.ID = tblDuplicates.ID
NeoPa 32,556
Expert Mod 16PB
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,556
Expert Mod 16PB
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,556
Expert Mod 16PB
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,556
Expert Mod 16PB
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...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
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,...
|
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...
|
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...
| |