i could write the new information into a temp table. since it has
everything, i could then simply delete all pertinent rows from the current
table, and write the temp tanle into the current table...
the issue of course would be how slow would this be....
i'm doing this for a web site...so it's going to be a real-time function....
here's my issue... i think it's rather simple..just can't see it for now...
i have a table (for discussion sake...)
uid store asset
3 tom book
3 tom table
so i can have duplicates in any of the columns.... i just can't have a row
where all of the columns are already in the table.... is there anyway of
doing an add to the table, short of having to do something like:
insert into foo (uid, store, asset) values (a,b,c) where a != uid and b !=
store and c != asset
i tried doing a straight replace... but, of course, it failed as i don't
have a field set to be unique.
isn't there an easy way to simply say "no duplicate rows" !!!!!
if there's a way to use replace to accomplish this.. how should the table be
setup...keeping in mind that i need to be able to have duplicate
fields..just not complete duplicate rows!!
From: Brent Baisley [mailto:brent@stripped]
Sent: Tuesday, May 13, 2003 7:27 AM
To: Bruce Douglas
Subject: Re: a database design question....
First, you should look into the REPLACE command. It's similar to insert
but does a duplicate check if you table is setup appropriately.
Second, specifying a column as UNIQUE will disallow duplicates.
Third, you can use temporary tables to clean up and repopulate your
main table. Put your list in a temporary table, then do a select left
join with you temporary table and the main table and have a where
clause to check for NULL values. Checking for null values with give you
a select result with only missing values.
SELECT * FROM TempTable LEFT JOIN MainTable ON
TempTable.ID=MainTable.ID WHERE MainTable.ID IS NULL
You obvious need to create your temporary tables, look in the
documentation on how to do this. You can do an INSERT SELECT to add new
values to your table.
On Monday, May 12, 2003, at 06:23 PM, Bruce Douglas wrote:
> i have a situation where i need to add a group of items to a table.
> i need to make sure that i don't add an item to the table, if it
> exists.... i also need to compare the items in the table with my list.
> the table contains items that aren't in the list, i need to delete
> them from
> the table...
> is there a way to set up a table definition to disallow for
> or, would it be easier/faster to simply do a delete on all the items
> the table... and then do a complete new insert for each item.....
> comments/criticisms welcome.....
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments