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

On 10-09-2011 23:16, jwcarlton wrote:
UPDATE table
SET var1="something_else"
WHERE var2="something"
AND var1 IS NULL
AND NOT EXISTS (SELECT var1
FROM table
WHERE var1="something_else" );


Maybe you have to change this subquery to match your exact needs.....



--
Luuk

I think that comes pretty close, thanks, Luuk. One question, though;
how do I make it SELECT based on dynamic variables from the first
SELECT?

I'm thinking the DEFAULT statement will work, but I don't know how to
use it correctly. The docs on this were pretty vague. Something like:

BEGIN
DECLARE @var4;

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

UPDATE table SET var1="something_else"
WHERE var2="something"
AND var1 IS NULL
AND NOT EXISTS (SELECT var1
FROM table
WHERE var1="something_else"
AND var3=var4);

END;

Is this possible, or am I going to have to write a PHP script for this?

On 10/09/11 23:16, jwcarlton wrote:
use the INSERT .. ON DUPLICATE KEY construct.

Good luck,
--
Willem Bogaerts

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

On Sep 12, 3:44*am, Willem Bogaerts <w.bogae... (AT) kratz (DOT) nl> wrote:
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.

On 9/12/2011 5:42 AM, jwcarlton wrote:
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.
jstucklex (AT) attglobal (DOT) net
==================

On 11-09-2011 22:43, jwcarlton wrote:
An example says more than 100 words... :

19:46:51 root@test[19]mysql> select * from tbl;
+----------+----------+
6 rows in set (0.00 sec)

19:46:54 root@test[20]mysql> set @a=67276832;
Query OK, 0 rows affected (0.00 sec)

19:47:08 root@test[21]mysql> select colA, @b:=colB from tbl where colA=@a;
+----------+----------+
1 row in set (0.00 sec)

19:47:17 root@test[22]mysql> select @b;
+----------+
1 row in set (0.00 sec)



--
Luuk

On Sep 12, 8:48*am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
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:

// Information sent from form
$get_section = mysql_real_escape_string($_GET['section']);
$get_user = mysql_real_escape_string($_GET['user']);

// Get all from username
$select_all_query = sprintf("SELECT * FROM table WHERE username='%s'",
$get_user);

$select_all_sth = mysql_query($select_all_query) or
die(mysql_error());

// Loop through all of his ads
while (list($id, $section, $cat, $subcat, $user, $title, $desc) =
mysql_fetch_row($select_all_sth)) {

// Check if an ad already exists in this section
$select_query = sprintf("
SELECT * FROM table
WHERE section='%s'
AND username='%s'
AND title='%s'
AND desc='%s';",
$section,
mysql_real_escape_string($user),
mysql_real_escape_string($title),
mysql_real_escape_string($desc)
);

$select_sth = mysql_query($select_query) or die(mysql_error());

// Doesn't already exist, insert
if (mysql_num_rows($select_sth) == 0) {
$insert_query = sprintf("
INSERT INTO table (id, section, cat, subcat, user, title, desc)
VALUES(NULL, '%s', '%s', '%s', '%s', '%s', '%s');",
$section,
mysql_real_escape_string($cat),
mysql_real_escape_string($subcat),
mysql_real_escape_string($user),
mysql_real_escape_string($title),
mysql_real_escape_string($desc)
);

$insert_sth = mysql_query($insert_query) or
die(mysql_error());
}
}

On Sep 12, 8:48*am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
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.

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

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.
jstucklex (AT) attglobal (DOT) net
==================