Something about the "INSERT" query

Databases
1

Hi everyone, I am pleased to be a member of this wonderful forum~ :smiley:

I have a question about using the “INSERT” query to insert multiple values into a table, consider the following sql:

INSERT INTO tablename(fielda,fieldb)
VALUES
(‘value1a’, ‘value1b’),
(‘value2a’, ‘value2b’),
(‘value3a’, ‘value3b’)

Suppose that the “fielda” field is the primary key of the table, and there exists a row in the table that takes the value “value3a” in the “fielda” field.

I want to execute the given SQL in a PHP script, clearly this will give me an SQL error since (‘value3a’, ‘value3b’) violates the uniqueness constraint in the table. Now I have the following questions:

  1. Will (‘value1a’, ‘value1b’) and (‘value2a’, ‘value2b’) be inserted into the table?

  2. If these values are inserted into the table, what shall I do in order to undo the partial effect of this SQL? (ie. to delete (‘value1a’, ‘value1b’) and (‘value2a’, ‘value2b’))

Thank you very much in advance for helping~ :slight_smile:

2

certainly not!!

but neither did i use phpmyadmin

how do you know that phpmyadmin did not silently and subtly change this –

INSERT INTO tablename(fielda,fieldb)
VALUES 
('value1a', 'value1b')
,('value2a', 'value2b')
,('value3a', 'value3b');

to this –

INSERT INTO tablename(fielda,fieldb) VALUES ('value1a', 'value1b');
INSERT INTO tablename(fielda,fieldb) VALUES ('value2a', 'value2b');
INSERT INTO tablename(fielda,fieldb) VALUES ('value3a', 'value3b');

that would certainly explain the results you got, yes?

try the single insert statement with multiple row constructors right in mysql, not in phpmyadmin, and see what you get

:slight_smile:

3

What the OP means is, that in his test the first two lines ARE inserted in the table, so after the query ended with a ‘duplicate key’ error, he still has two new lines in his table. As you can see in the vid he published (nice vid by the way :slight_smile: ).

While Rudy claims that in his test this doesn’t happen.

4

??

When you inserted data to the table, did you miss the yellow line across the top of your page saying duplicate key value3a for Primary key?

however, it seems that MySQL doesn’t support this property.

not sure what you mean by that. what property?

don’t know what you mean by partial effect either. But this is how to delete records from your table. (Too tired to recall if the brackets are necessary in the delete statement).

delete from table
where ( fielda = ‘value1a’
or
fielda= ‘value2a’
)

bazz

5

wow, that’s interesting, isn’t it

(nice video, too)

i’ve checked the manual and it says that unless you use IGNORE, any duplicates cause the statement to be aborted

it sure looks like this is different from what i thought it was supposed to do, which is to roll back to the beginning of the statement

so, to get back to your original question, i guess to “undo the partial effect” you will have to delete the rows which got inserted up to the point of the duplicate

how you are supposed to isolate which key values those are, i have no idea…

6

Thank you very much r937, but I still get the same result without using phpmyadmin, please check it out here: http://www.youtube.com/watch?v=yqW7fInEIN8

7

what happened when you tested it?

:cool:

8

Thanks r937, I was expecting to get the same result that you have got, however, it seems that MySQL doesn’t support this property.

I have used your code to test on MySQL, here is the recording of the test: http://www.youtube.com/watch?v=WXWyi4RkZns

Did you use Oracle for the test?

9

Strange. when I do inserts that cause a duplication of primary key, the statement is always aborted. And that’s when I use phpMyAdmin.

After seeing the video, I’m gonna jump into using the command line. :wink:

bazz

10

I don’t know much about the technicalities of mysql but, could it be possible that something could be wrong with the installation?

what version you using?

11

thanks r937~ :slight_smile:

I have tested that on MySQL, and it is a yes to the first question. Now I wonder how can I remove the partial effect of this query?

Many thanks.

12

i don’t believe there is a partial effect – the query works or it doesn’t

here, i’ll test it for ya

first, let’s set it up: [indent]Suppose that the “fielda” field is the primary key of the table, and there exists a row in the table that takes the value “value3a” in the “fielda” field.[/indent]

CREATE TABLE tablename
( fielda VARCHAR(9) NOT NULL PRIMARY KEY
, fieldb VARCHAR(37)
);
INSERT INTO tablename(fielda,fieldb)
VALUES ('value3a', 'already exists') 
;
SELECT * FROM tablename
;
[COLOR="Blue"][U]fielda[/U]   [U]fieldb[/U]
value3a  already exists[/COLOR]

now let’s do your test –

INSERT INTO tablename(fielda,fieldb)
VALUES 
('value1a', 'value1b')
,('value2a', 'value2b')
,('value3a', 'value3b') 
;
[COLOR="Red"]SQL Error (1062): Duplicate entry 'value3a' for key 1[/COLOR]

now let’s see if there are any partial results

SELECT * FROM tablename
;
[COLOR="Blue"][U]fielda[/U]   [U]fieldb[/U]
value3a  already exists[/COLOR]

nope :cool: