Counterglow Forums  

Go Back   Counterglow Forums > General Discussions > Technoglow

Reply
 
Thread Tools Display Modes
Old 12-05-2006, 18:16:27   #1
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
SQL Query

I have a table with a single column, which is delared as a MUL key (which means that it can have multiple rows with the same value, I'm not sure how this is different from it not being a key). Assume that I can't alter the table to make it a primary key. How can I insert a value into the table when there is no row containing that value, and do nothing when the value is already in the table, using only one query? That is, a replacement for INSERT ... ON DUPLICATE KEY UPDATE or REPLACE, but without needing a key.

I can do it with an UPDATE and an INSERT, but one query would be better (because then I would have to search for the code that gets how many rows were affected). This is in MySQL.

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Old 12-05-2006, 20:12:52   #2
Venom
Look out behind you!
 
Venom's Avatar
 
Join Date: Nov 2001
Location: Rudolph's stable
You might be fucked on that one.
__________________
TO MY BALLS!!!!!
or
TO LazyView!!!!

Venom is offline   Reply With Quote
Old 12-05-2006, 20:53:12   #3
Qaj the Fuzzy Love Worm
Not National Zombie Awareness Month
 
Qaj the Fuzzy Love Worm's Avatar
 
Join Date: Jan 2002
Location: Gnawing at your brain
Don't know about MySQL, but in T-SQl (or whatever SQL*Server's SQL is called) you can try

Code:
INSERT INTO newtable
SELECT DISTINCT value_to_be_inserted
FROM newtable
WHERE value_to_be_inserted NOT IN (SELECT DISTINCTcolumn FROM newtable)
That's just off the top of my head. I should try it out...

EDIT: value_to_be_inserted would be either a contant (such as 'newvalue') or a variable within a procedural script.

SECOND EDIT: added DISTINCT commands or duplicate inserts occur
__________________
Not annoying anyone with my signature since 2011!

Last edited by Qaj the Fuzzy Love Worm; 12-05-2006 at 21:05:32.
Qaj the Fuzzy Love Worm is offline   Reply With Quote
Old 12-05-2006, 20:57:46   #4
Venom
Look out behind you!
 
Venom's Avatar
 
Join Date: Nov 2001
Location: Rudolph's stable
Still wouldn't handle the update.
__________________
TO MY BALLS!!!!!
or
TO LazyView!!!!

Venom is offline   Reply With Quote
Old 12-05-2006, 21:00:13   #5
Darkstar
will bitch for beer
 
Darkstar's Avatar
 
Join Date: Nov 2001
Location: Rocket City
Does MySQL have a DECODE statement? If it did, then you could do it in one statement.
__________________
> clue++;
> display clue;
-878923403
Darkstar is offline   Reply With Quote
Old 12-05-2006, 21:00:51   #6
Qaj the Fuzzy Love Worm
Not National Zombie Awareness Month
 
Qaj the Fuzzy Love Worm's Avatar
 
Join Date: Jan 2002
Location: Gnawing at your brain
What update? From what I understand of the question, this does exactly what is needed: ie. inserts the new value if it doesn't exist, and does nothing if the value already exists.

BTW, I just tested it in SQL*Server, and it was 100% successful, BUT only if at least one record already exists in the table. Not sure why it doesn't work for an empty table, probably something to do with the first SELECT
__________________
Not annoying anyone with my signature since 2011!
Qaj the Fuzzy Love Worm is offline   Reply With Quote
Old 12-05-2006, 21:10:20   #7
Qaj the Fuzzy Love Worm
Not National Zombie Awareness Month
 
Qaj the Fuzzy Love Worm's Avatar
 
Join Date: Jan 2002
Location: Gnawing at your brain
Final edit: This seems to get rid of the no insert into the empty table problem:

Code:
INSERT INTO newtable
SELECT DISTINCT value_to_be_inserted
WHERE value_to_be_inserted NOT IN (SELECT DISTINCTcolumn FROM newtable)

Again, not sure if it'll work with MySQL, but SQL*Server runs fine. It could be you can get it running with a few syntax modifications if it doesn't.
__________________
Not annoying anyone with my signature since 2011!
Qaj the Fuzzy Love Worm is offline   Reply With Quote
Old 12-05-2006, 21:11:17   #8
Darkstar
will bitch for beer
 
Darkstar's Avatar
 
Join Date: Nov 2001
Location: Rocket City
Quote:
Originally posted by Sir Penguin
That is, a replacement for INSERT ... ON DUPLICATE KEY UPDATE or REPLACE, but without needing a key.

I can do it with an UPDATE and an INSERT, but one query would be better (because then I would have to search for the code that gets how many rows were affected).
__________________
> clue++;
> display clue;
-878923403
Darkstar is offline   Reply With Quote
Old 12-05-2006, 21:30:04   #9
Venom
Look out behind you!
 
Venom's Avatar
 
Join Date: Nov 2001
Location: Rudolph's stable
Sue me. I can only read at a first grade level.
__________________
TO MY BALLS!!!!!
or
TO LazyView!!!!

Venom is offline   Reply With Quote
Old 12-05-2006, 21:31:57   #10
Darkstar
will bitch for beer
 
Darkstar's Avatar
 
Join Date: Nov 2001
Location: Rocket City
That was for Qaj, Mr. Venom. I thought I'd pare down the original text to highlight SP's requirements for him.
__________________
> clue++;
> display clue;
-878923403
Darkstar is offline   Reply With Quote
Old 12-05-2006, 21:46:04   #11
Venom
Look out behind you!
 
Venom's Avatar
 
Join Date: Nov 2001
Location: Rudolph's stable
Yeah, but I was still wrong.
__________________
TO MY BALLS!!!!!
or
TO LazyView!!!!

Venom is offline   Reply With Quote
Old 12-05-2006, 22:19:46   #12
Qaj the Fuzzy Love Worm
Not National Zombie Awareness Month
 
Qaj the Fuzzy Love Worm's Avatar
 
Join Date: Jan 2002
Location: Gnawing at your brain
But why would there need to be an update on the table, if indeed it is specified with a single column, as SP states?

The initial definition of the problem is contradictory in parts. We need the Penguin to clarify. Typical that he should sow the seeds of conflict and then leave. It's just like a Canadian to do that, bastard.
__________________
Not annoying anyone with my signature since 2011!
Qaj the Fuzzy Love Worm is offline   Reply With Quote
Old 13-05-2006, 00:39:46   #13
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
Yeah, it doesn't actually need to update, I was just using that as an example of something that would return whether or not the value was in the table. I ended up using a SELECT and an INSERT.

I think Qaj's query would work, except it does three queries.

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Old 13-05-2006, 01:43:21   #14
Venom
Look out behind you!
 
Venom's Avatar
 
Join Date: Nov 2001
Location: Rudolph's stable
In any case. I think you're still shit out of luck. I've struggled with a very similar case many a time and never come up with a good solution.
__________________
TO MY BALLS!!!!!
or
TO LazyView!!!!

Venom is offline   Reply With Quote
Old 13-05-2006, 02:05:41   #15
Qaj the Fuzzy Love Worm
Not National Zombie Awareness Month
 
Qaj the Fuzzy Love Worm's Avatar
 
Join Date: Jan 2002
Location: Gnawing at your brain
It's a single SQL statement. Transact-SQL INSERT can have a SELECT component, and SELECTs can have embedded SELECTs. It's still only one statement though
__________________
Not annoying anyone with my signature since 2011!
Qaj the Fuzzy Love Worm is offline   Reply With Quote
Old 13-05-2006, 02:17:55   #16
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
Quote:
Originally posted by Sir Penguin How can I insert a value into the table when there is no row containing that value, and do nothing when the value is already in the table, using only one query?
It's one statement that has three queries. I might let you get away with saying it has two queries, since the MySQL documentation lists an INSERT...SELECT syntax, but there's still another nested SELECT.

Thanks, Venom. It's not a big deal in this case, since the query won't be made too often (it reports a post as spam in our forum software), but it's nice to know.

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Old 16-05-2006, 23:21:21   #17
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
Is it faster to do one UPDATE query on many values, or a few UPDATE queries on consolidated values?

I'm writing code that will modify a user's postcount when posts are deleted/undeleted. I've written two UPDATE queries that should do the same thing:

The first is in a foreach loop that iterates through an array of userIDs ($userID) associated to how much their postcount should be modified as a result of this delete/undelete ($delta):

PHP Code:
"UPDATE forum_user ".
"SET totalPosts=if(totalPosts+{$delta}<0, 0, totalPosts+{$delta}) ".
"WHERE forumUserID={$userID}" 
The second does everything in the database, in one long query. It adds $delta to the postcount once for every post being deleted/undeleted, instead of consolidating posts and making only one update for each user.

PHP Code:
"UPDATE forum_user u, {$forum->getCommentTableName()} c ".
"SET u.totalPosts=if(u.totalPosts+{$delta}<0, 0, u.totalPosts+{$delta}) ".
"WHERE c.commentID IN (".dbLib::dbSafeList($commentIDs).") AND u.forumUserID=c.forumUserID" 
My boss just walked in, so I'm going to have to give more details later.

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet

Last edited by Sir Penguin; 17-05-2006 at 00:29:11.
Sir Penguin is offline   Reply With Quote
Old 17-05-2006, 00:27:39   #18
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
Never mind, the first way is pretty stupid. It requires a lot of extra work in PHP-space that can be done way better in the second query. Of course, it doesn't work yet...

For posterity, does adding more affected rows to a single UPDATE query have a similar speed impact to running multiple UPDATEs that each modify a single row?

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Old 17-05-2006, 03:18:33   #19
Qaj the Fuzzy Love Worm
Not National Zombie Awareness Month
 
Qaj the Fuzzy Love Worm's Avatar
 
Join Date: Jan 2002
Location: Gnawing at your brain
Doing multiple UPDATEs should have a bigger impact on the DB than one UPDATE that does multiple records. Though for small databases or fast servers you probably won't see much difference in elapsed time until you go deep into sig figs.
__________________
Not annoying anyone with my signature since 2011!
Qaj the Fuzzy Love Worm is offline   Reply With Quote
Old 17-05-2006, 12:04:39   #20
Nav
Never gonna be as ugly as Lurker
 
Nav's Avatar
 
Join Date: Nov 2001
Location: Everywhere...
Out of interest how would you do multiple updates in one query in MySQL? Something I haven't come across before (I do multiple inserts all the time).

And query within queries is only supported by MySQL 4+
Nav is offline   Reply With Quote
Old 17-05-2006, 15:42:39   #21
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
I suppose it also depends on how the table is indexed.

You do multiple UPDATEs in one by having a WHERE clause that selects multiple rows. In the SET clause, any field referenced on the right hand side of an assignment holds the field's old value in the row currently being modified (e.g. "... SET totalPosts=totalPosts+1 WHERE commentID IN (1, 3, 5, 7, 9)" is the same as incrementing totalPosts in the records with commentIDs equal to 1, 3, 5, 7, and 9).

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Old 17-05-2006, 19:46:56   #22
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
I was wrong, a column as an rvalue in the SET clause is defined at the beginning of the UPDATE, and is not updated as the UPDATE runs. So if I have several "SET totalPosts=totalPosts+1" clauses updating the same row, the final value will be the original value + 1 instead of the original value + n (if there were n updates to that row).

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Old 17-05-2006, 22:36:02   #23
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
Here's what I'm working on right now:

PHP Code:
"UPDATE forum.forum_user AS u, (" .
        
"SELECT forumUserID, COUNT(forumUserID) AS commentCount FROM {$forum->getCommentTableName()} " .
        
"WHERE forumID={$forumID} AND commentID IN (".dbLib::dbSafeList($commentIDs).") GROUP BY forumUserID" .
") AS c " .
"SET u.totalPosts=if(u.totalPosts+({$step}*c.commentCount) < 0, 0, u.totalPosts+({$step}*c.commentCount)) " .
"WHERE u.forumUserID=c.forumUserID" 
It doesn't work yet; it only updates one post count, and it does that incorrectly.

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Old 19-05-2006, 15:47:54   #24
Sir Penguin
Wise sprite
 
Sir Penguin's Avatar
 
Join Date: Nov 2001
Location: command.com
PHP Code:
"UPDATE forum.forum_user AS u RIGHT JOIN (" .
        
"SELECT forumUserID, COUNT(*) AS commentCount FROM {$forum->getCommentTableName()} AS comment " .
        
"WHERE forumID={$forumID} AND commentID IN (".dbLib::dbSafeList($commentIDs).") AND (forumUserID != 0) " .
        
"AND comment.status LIKE ".(($step<0)?"'APPROVED'":"'DELETED'")." GROUP BY forumUserID" .
") AS c USING (forumUserID) " .
"SET u.totalPosts=if(u.totalPosts+({$step}*c.commentCount) < 0, 0, u.totalPosts+({$step}*c.commentCount)) " 
I'm not sure why using a right join instead of a cross join works, but it does. With the cross join, it was only updating the first row from the nested SELECT (it actually did that correctly, but I kept refreshing the page to test it, which re-ran the query ). Weird.

SP
__________________
Whether 'tis nobler in the plane to suffer
The asps and adders of outrageous fortune,
Or to take arms against a sea of bitch-ass motherfuckers
And by opposing fuck them up?

-- Samuel L. Jackson as Hamlet
Sir Penguin is offline   Reply With Quote
Reply
Forum Jump

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT. The time now is 23:35:55.


Powered by vBulletin® Version 3.8.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© Counterglow 2001-2012. All rights reserved.