PDA

View Full Version : SQL Query


Sir Penguin
12-05-2006, 18:16:27
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

Venom
12-05-2006, 20:12:52
You might be fucked on that one.

Qaj the Fuzzy Love Worm
12-05-2006, 20:53:12
Don't know about MySQL, but in T-SQl (or whatever SQL*Server's SQL is called) you can try



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

Venom
12-05-2006, 20:57:46
Still wouldn't handle the update.

Darkstar
12-05-2006, 21:00:13
Does MySQL have a DECODE statement? If it did, then you could do it in one statement.

Qaj the Fuzzy Love Worm
12-05-2006, 21:00:51
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

Qaj the Fuzzy Love Worm
12-05-2006, 21:10:20
Final edit: This seems to get rid of the no insert into the empty table problem:



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.

Darkstar
12-05-2006, 21:11:17
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).

Venom
12-05-2006, 21:30:04
Sue me. I can only read at a first grade level.

Darkstar
12-05-2006, 21:31:57
That was for Qaj, Mr. Venom. I thought I'd pare down the original text to highlight SP's requirements for him.

Venom
12-05-2006, 21:46:04
Yeah, but I was still wrong.

Qaj the Fuzzy Love Worm
12-05-2006, 22:19:46
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.

Sir Penguin
13-05-2006, 00:39:46
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. :p

SP

Venom
13-05-2006, 01:43:21
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.

Qaj the Fuzzy Love Worm
13-05-2006, 02:05:41
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 :D

Sir Penguin
13-05-2006, 02:17:55
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. :D

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

Sir Penguin
16-05-2006, 23:21:21
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):

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

"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

Sir Penguin
17-05-2006, 00:27:39
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

Qaj the Fuzzy Love Worm
17-05-2006, 03:18:33
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.

Nav
17-05-2006, 12:04:39
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+

Sir Penguin
17-05-2006, 15:42:39
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

Sir Penguin
17-05-2006, 19:46:56
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

Sir Penguin
17-05-2006, 22:36:02
Here's what I'm working on right now:

"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.commentCou nt) < 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

Sir Penguin
19-05-2006, 15:47:54
"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.commentCou nt) < 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 :rolleyes: ). Weird.

SP