CODEWITHSUNDEEP
phpmysqlsql
Matteo
Matteo

Reputation: 2624

Two INSERT INTO statements for the same row SQL/php

I have been trying so hard and I can't find a solution for this problem. I have two different INSERT statements: 

INSERT INTO `logList`(`User_ID`) SELECT `User_ID` FROM `userList` WHERE keyname='somevalue';
INSERT INTO `logList`(`ttime`) VALUES (CURRENT_TIMESTAMP);

If I execute them, they will of course insert 2 different rows. How can I make a single INSERT that returns only one row? Thank you in advance.

Upvotes: 2

Views: 202

Answers (3)

Lajos Arpad
Lajos Arpad

Reputation: 76551

You have this command:

INSERT INTO `logList`(`User_ID`) SELECT `User_ID` FROM `userList` WHERE keyname='somevalue';
INSERT INTO `logList`(`ttime`) VALUES (CURRENT_TIMESTAMP);

Which will create two rows and you want to create a single row. You can do that using a single command:

insert into loglist(USER_ID, ttime)
select User_ID, now()
from userList
where keyname = 'somevalue';

However, even though this is a solution, it is not a very elegant one. You should alter loglist and modify ttime to have the default value of CURRENT_TIMESTAMP and then you can simplify your insert command like this:

insert into loglist(USER_ID)
select User_ID
from userList
where keyname = 'somevalue';

this should be the normal behavior, since your loglist table should probably have the timestamp of the insertion moment anyway.

Upvotes: 0

gengisdave
gengisdave

Reputation: 2050

As you ask, you can do it in one single Insert:

INSERT INTO `logList`(`User_ID`, `ttime`)
    SELECT `User_ID`, CURRENT_TIMESTAMP
    FROM `userList`
    WHERE keyname='somevalue';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I am going to assume that this is what you really want:

INSERT INTO logList (User_ID, ttime)
    SELECT User_ID, CURRENT_TIMESTAMP
    FROM userList
    WHERE keyname = 'somevalue';

I doubt you want two separate rows, one with the user and NULL for time and the other with NULL for user and a value of the time.

I should note that you can define the loglist so ttime is defaulted to the current timestamp on the insert. The use of such defaults is described in the documentation.

If you did that, then:

INSERT INTO logList (User_ID)
    SELECT User_ID
    FROM userList
    WHERE keyname = 'somevalue';

would always set ttime.

Upvotes: 4

Related Questions