INSERT INTO...SELECT, unless something already exists?

#11

On Sep 12, 7:24*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

On 9/12/2011 6:53 PM, jwcarlton wrote:

On Sep 12, 8:48 am, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> *wrote:
On 9/12/2011 5:42 AM, jwcarlton wrote:

On Sep 12, 3:44 am, Willem Bogaerts<w.bogae... (AT) kratz (DOT) nl> * *wrote:
On 10/09/11 23:16, jwcarlton wrote:

I'm copying one row into the same table, leaving one field empty.
Then, I'm running a second line to fill that field if it's empty. Like
so:

INSERT INTO table
* * SELECT NULL, NULL, var2, var3
* * FROM table
* * WHERE var2="something";

UPDATE table SET var1="something_else" WHERE var2="something" AND var1
IS NULL;

("NULL, NULL" represents "id, var1", where "id" is an autoincrement)

This will grab, for example, 100 rows and copy them for me, changing
the one value that I designate.

What I'm wanting to do is change the INSERT so that it only insertsIF
there's not already a row in the table that has the same var1 that
I've entered, and the same var2 and var3 as the one being selected.
This will help me to prevent accidental duplicates.

Any suggestions would be greatly appreciated! TIA,

Jason

Is there a unique index that would prevent the duplicates? If so, just
use the INSERT .. ON DUPLICATE KEY construct.

Good luck,
--
Willem Bogaerts

Application smith
Kratz B.V.http://www.kratz.nl/

No, I'm afraid not. :-( *In this case, I'm actually working with
classified ads, and it's a regular occurrence that one ad would fit
into multiple sections. So when I copy the row, I'm checking the
username, section name, and title of the ad, but none of these fields
would technically be unique.

The more I research, I don't think that there's a way to do it without
a PHP script of some sort.

Is the combination of the three unique?

How would you determine duplicates in PHP?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

I was trying to not make it too confusing by giving a ton of field
names. But I would compare the section, category, subcategory,
username, title, and description.

In plain English, the code would say "copy category, subcategory,
username, title, and description, then change section to whatever is
designated, UNLESS a row already exists with the new section,
category, subcategory, username, title, and description".

This wouldn't be limited to a single row, though. Someone could
potentially have dozens of ads, and I may want all of them copied to
another section (or several sections).

The code that I posted in the first post works fine when I copy the
rows to a second section. But, it's not uncommon that I copied some
yesterday, and today the username created more ads; so, I want to only
copy the new ones from today. Or, I might want to copy all of them
from Section A to Section C, where yesterday I just copied them to
Section B. In that case, the code in the first post would copy all
from Section A AND Section B to Section C, which would give me 2
copies of each ad in Section C instead of the desired 1 copy.

If I did it in PHP, I would probably have to select the row, store the
columns as variables, then run the insert script. This isn't ran or
tested, so there could be typos and errors, but I'm thinking something
like:

snip code

Well, it looks to me like you have a major design problem in your
database. *You shouldn't need to have the same information multiple
times. *Rather, you should have it once, and have a link table between
the posting and the section(s) it should be in.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Can you send me to something explaining your reference to a "link
table"? I don't know what you mean.

#12

On 9/12/2011 8:08 PM, jwcarlton wrote:

Quote:

On Sep 12, 7:24 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 9/12/2011 6:53 PM, jwcarlton wrote:

On Sep 12, 8:48 am, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 9/12/2011 5:42 AM, jwcarlton wrote:

On Sep 12, 3:44 am, Willem Bogaerts<w.bogae... (AT) kratz (DOT) nl> wrote:
On 10/09/11 23:16, jwcarlton wrote:

I'm copying one row into the same table, leaving one field empty.
Then, I'm running a second line to fill that field if it's empty. Like
so:

INSERT INTO table
SELECT NULL, NULL, var2, var3
FROM table
WHERE var2="something";

UPDATE table SET var1="something_else" WHERE var2="something" AND var1
IS NULL;

("NULL, NULL" represents "id, var1", where "id" is an autoincrement)

This will grab, for example, 100 rows and copy them for me, changing
the one value that I designate.

What I'm wanting to do is change the INSERT so that it only inserts IF
there's not already a row in the table that has the same var1 that
I've entered, and the same var2 and var3 as the one being selected.
This will help me to prevent accidental duplicates.

Any suggestions would be greatly appreciated! TIA,

Jason

Is there a unique index that would prevent the duplicates? If so, just
use the INSERT .. ON DUPLICATE KEY construct.

Good luck,
--
Willem Bogaerts

Application smith
Kratz B.V.http://www.kratz.nl/

No, I'm afraid not. :-( In this case, I'm actually working with
classified ads, and it's a regular occurrence that one ad would fit
into multiple sections. So when I copy the row, I'm checking the
username, section name, and title of the ad, but none of these fields
would technically be unique.

The more I research, I don't think that there's a way to do it without
a PHP script of some sort.

Is the combination of the three unique?

How would you determine duplicates in PHP?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

I was trying to not make it too confusing by giving a ton of field
names. But I would compare the section, category, subcategory,
username, title, and description.

In plain English, the code would say "copy category, subcategory,
username, title, and description, then change section to whatever is
designated, UNLESS a row already exists with the new section,
category, subcategory, username, title, and description".

This wouldn't be limited to a single row, though. Someone could
potentially have dozens of ads, and I may want all of them copied to
another section (or several sections).

The code that I posted in the first post works fine when I copy the
rows to a second section. But, it's not uncommon that I copied some
yesterday, and today the username created more ads; so, I want to only
copy the new ones from today. Or, I might want to copy all of them
from Section A to Section C, where yesterday I just copied them to
Section B. In that case, the code in the first post would copy all
from Section A AND Section B to Section C, which would give me 2
copies of each ad in Section C instead of the desired 1 copy.

If I did it in PHP, I would probably have to select the row, store the
columns as variables, then run the insert script. This isn't ran or
tested, so there could be typos and errors, but I'm thinking something
like:

snip code

Well, it looks to me like you have a major design problem in your
database. You shouldn't need to have the same information multiple
times. Rather, you should have it once, and have a link table between
the posting and the section(s) it should be in.

Can you send me to something explaining your reference to a "link
table"? I don't know what you mean.

The first thing you need to do is study up on Database Normalization.
That will explain a lot for you.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#13

Quote:

Well, it looks to me like you have a major design problem in your
database. *You shouldn't need to have the same information multiple
times. *Rather, you should have it once, and have a link table between
the posting and the section(s) it should be in.

Can you send me to something explaining your reference to a "link
table"? I don't know what you mean.

The first thing you need to do is study up on Database Normalization.
That will explain a lot for you.

I actually researched the logic there awhile back, so the term isn't
foreign to me. But if I understand the logic behind linking tables
correctly, the idea would be to have 2 tables instead of one; the
first table with the username and ad data, then a second table with 2
columns; one with the ID, and the second with the section.

Wouldn't this require 2 queries for each pageload, though, as opposed
to the current 1 query? Meaning, on a page that shows 40 ads, the page
would have to query the first table to find which IDs to show, then
use that to query the second table for the actual data to show?

While I get that the database itself would be smaller, that seems like
it would take a lot more processing time in the script itself. And,
running 2 queries instead of one seems like it would require more
processing time for the database. Or am I completely misunderstanding
the concept?

#14

On 2011-09-13 04:47, jwcarlton wrote:
[...]

Quote:

Wouldn't this require 2 queries for each pageload, though, as opposed
to the current 1 query? Meaning, on a page that shows 40 ads, the page
would have to query the first table to find which IDs to show, then
use that to query the second table for the actual data to show?

I suggest you take the time to at least read a tutorial on sql. You will
find one at:

http://www.firstsql.com/tutor.htm

When you get a basic understanding of that, I suggest you get a book
used in a database class at some university. If you can cope with
Swedish ad's, Hugh Darwen's An introduction to relational database
theory is downloadable at

http://bookboon.com/se/student/it/an...atabase-theory

To download you will have to fill in the form to the right of the page

1. Nyhetsbrev (Newsletter), fill in an email address
2.
3. Vad arbetar du med? (What are you working with?), choose occupation
4. Område arbetar du inom? (What area are you working within?)

/Lennart

[...]

#15

On 9/12/2011 10:47 PM, jwcarlton wrote:

Quote:

Well, it looks to me like you have a major design problem in your
database. You shouldn't need to have the same information multiple
times. Rather, you should have it once, and have a link table between
the posting and the section(s) it should be in.

Can you send me to something explaining your reference to a "link
table"? I don't know what you mean.

The first thing you need to do is study up on Database Normalization.
That will explain a lot for you.

I actually researched the logic there awhile back, so the term isn't
foreign to me. But if I understand the logic behind linking tables
correctly, the idea would be to have 2 tables instead of one; the
first table with the username and ad data, then a second table with 2
columns; one with the ID, and the second with the section.

Wouldn't this require 2 queries for each pageload, though, as opposed
to the current 1 query? Meaning, on a page that shows 40 ads, the page
would have to query the first table to find which IDs to show, then
use that to query the second table for the actual data to show?

While I get that the database itself would be smaller, that seems like
it would take a lot more processing time in the script itself. And,
running 2 queries instead of one seems like it would require more
processing time for the database. Or am I completely misunderstanding
the concept?

Actually, it would require three tables - the third one being section
information. And once you've figured that out, you need to learn basic
SQL. You can fetch data from more than one table at a time.

You really need to go back to database 101 and learn how to use
relational databases properly. It will make your life so much easier.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#16

On Mon, 12 Sep 2011 19:47:36 -0700 (PDT), jwcarlton wrote:

Quote:

While I get that the database itself would be smaller, that seems like
it would take a lot more processing time in the script itself. And,
running 2 queries instead of one seems like it would require more
processing time for the database. Or am I completely misunderstanding
the concept?

Queries are (in general) very fast. Properly indexed, they're faster
than reading extraneous data off disk (a query will know EXACTLY where
to go to get what it needs), and they're much, much, *much* faster
than sending data over a network. The amount of extra time running two
properly-designed queries on a properly-designed database compared to
one will get completely buried in the amount of time it takes to send
adverts to a client browser twenty network hops away. Don't bother
optimizing prematurely.

--
Revenge is an integral part of forgiving and forgetting.
-- The BOFH

#17

On Mon, 12 Sep 2011 16:09:19 -0700 (PDT), jwcarlton wrote:

Quote:

Oh, and to answer your question about whether the combination would be
unique... sorta, but not really.

For example, someone might have a litter of 8 puppies for sale, and
the uncreative poster might just say "beagle" in the title, then
"puppies" in the description, and show a different picture for each
puppy. So, even though the database would see identical rows, they
technically wouldn't be identical.

Using the picture names wouldn't work, either; to prevent accidental
overwrites of pictures, I change the picture name to "$id . '.jpg'"
when the ad is written.

So there still has to be a little human interaction to prevent
duplicates. But, this also means that I can't create a UNIQUE index to
work with.

This goes even further to support the use of artifical keys on your
records and link those together than trying to manage things with a
bunch of "mostly unique but not always" combinations of fields.

--
7. When I've captured my adversary and he says, "Look, before you kill
me, will you at least tell me what this is all about?" I'll say,
"No." and shoot him. On second thought I'll shoot him then say "No."
--Peter Anspach's list of things to do as an Evil Overlord

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

INSERT INTO...SELECT, unless something already exists?

comp.databases.mysql comp.databases.mysql

Re: INSERT INTO...SELECT, unless something already exists? - 09-12-2011 , 07:08 PM

Re: INSERT INTO...SELECT, unless something already exists? - 09-12-2011 , 08:42 PM

Re: INSERT INTO...SELECT, unless something already exists? - 09-12-2011 , 09:47 PM

Re: INSERT INTO...SELECT, unless something already exists? - 09-13-2011 , 12:27 AM

Re: INSERT INTO...SELECT, unless something already exists? - 09-13-2011 , 04:18 AM

Re: INSERT INTO...SELECT, unless something already exists? - 09-13-2011 , 08:45 AM

Re: INSERT INTO...SELECT, unless something already exists? - 09-13-2011 , 08:49 AM