List:General Discussion« Previous MessageNext Message »
From:Bruce Douglas Date:May 13 2003 7:38pm
Subject:RE: a database design question....
View as plain text  
another possibility...

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

thanks....


ARRGGHH!!!!!

here's my issue... i think it's rather simple..just can't see it for now...
i have a table (for discussion sake...)

table foo
	uid
	store
	asset

	uid	store	asset
	3	tom	book
	3	tom	table
	etc....

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


thanks

bruce
bedouglas@stripped


-----Original Message-----
From: Brent Baisley [mailto:brent@stripped]
Sent: Tuesday, May 13, 2003 7:27 AM
To: Bruce Douglas
Cc: mysql@stripped
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:

> hey...
>
> i have a situation where i need to add a group of items to a table.
> however,
> i need to make sure that i don't add an item to the table, if it
> already
> exists.... i also need to compare the items in the table with my list.
> if
> 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
> repeat/duplicate
> entries....
>
> or, would it be easier/faster to simply do a delete on all the items
> from
> the table... and then do a complete new insert for each item.....
>
> comments/criticisms welcome.....
>
> thanks
>
> bruce
> bedouglas@stripped
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Thread
Select in selectBogdan Soos7 May
  • LoggingJerry7 May
  • Re: Select in select(Terry Riley)7 May
  • re: Select in selectEgor Egorov7 May
re: Select in selectRichard Morton8 May
  • Password Encryption...Doug Coning8 May
    • Re: Password Encryption...Jeff Shapiro8 May
      • Re: Password Encryption...Curtis Maurand12 May
        • a database design question....Bruce Douglas13 May
          • Re: a database design question....Becoming Digital15 May
  • Re: Password Encryption...Michael T. Babcock9 May
RE: a database design question....Bruce Douglas13 May
  • Re: a database design question....Bruce Feist14 May
    • RE: a database design question....Bruce Douglas14 May
      • Re: a database design question....Bruce Feist14 May
      • RE: a database design question....Curtis Maurand16 May
        • Re: a database design question....Bruce Feist16 May
        • Re: a database design question....Martin Gainty16 May
  • RE: a database design question....Curtis Maurand14 May
    • Re: a database design question....Michael Brunson14 May
    • questionRandy Paries14 May
  • RE: a database design question....Jennifer Goodie14 May
RE: a database design question....Bruce Douglas14 May