how I make a single query for both insert and update?

[thara]

Suppose we have a mysql table something like this

id, userid, days, start, and close

and we have data for each columns like this -

1, 3, mon, 01.00, 02.00,

2, 3, tue, 03.00, 05.00,

3, 3, wed, 04.00, 06.00,

4, 3, thu, 05.00, 07.00,

5, 3, fri, 06.00, 08.00,

6, 3, sat, 07.00, 10.00,

7, 3, sun, 08.00, 12.00,

with this data, I need to update or insert my table. (if userid is not exist in table it should be insert or if userid exist in db it should be update.)

Can I know, is there a way to make a mysql single query for this? I tried it with INSERT ... ON DUPLICATE KEY UPDATE there I can only edit single row that mean I can not insert or update table with multiple rows using INSERT ... ON DUPLICATE KEY UPDATE.

At the moment I have used 2 different queries for inset and update

This is my insert query -

$q = "INSERT INTO useravailability (userid, days, opentime, closetime)

        VALUES (?, 'Monday', ?, ?),

                 (?, 'Tuesday', ?, ?),

                 (?, 'Wednesday', ?, ?),

                 (?, 'Thursday', ?, ?),

                 (?, 'Friday', ?, ?),        

                 (?, 'Saturday', ?, ?),                            

                 (?, 'Sunday', ?, ?)";                            

            $stmt = mysqli_prepare($dbc, $q);

            mysqli_stmt_bind_param($stmt, 'issississississississ',

                                           $userId, $monOpen, $monClose,

                                           $userId, $tueOpen, $tueClose,

                                           $userId, $wedOpen, $wedClose,

                                           $userId, $thuOpen, $thuClose,

                                           $userId, $friOpen, $friClose,

                                           $userId, $satOpen, $satClose,

                                           $userId, $sunOpen, $sunClose);                            

            // Execute the query:

            mysqli_stmt_execute($stmt);

[StefanRSA]

Why not doing a simple Lookup first to see if the userid exists and then do the Update or insert?

[thara]

Why not doing a simple Lookup first to see if the userid exists and then do the Update or insert?

 

Yes Its ok. I have already done it. But reason to post here, I am looking for a solution with single query for both insert and update using INSERT ... ON DUPLICATE KEY UPDATE.

[Barand]

To use ON DUPLICATE KEY you first need a unique key. Userid is not unique so the unique key would need to be (userid, days).

 

Then when you try to add, say, (3, 'Monday') again there would be a duplicate so you can update the time fields

[thara]

@Barand, Thanks for response. 

 

I tried it, this is my table structure -

CREATE TABLE availability (
    availabilityid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    userid SMALLINT UNSIGNED NOT NULL,
    days VARCHAR(10) NOT NULL,
    opentime VARCHAR( DEFAULT NULL,
    closetime VARCHAR( DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE (userid, days)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[Barand]

I tried it,.....

 

And...?

[thara]

yes I tried it like this and it works me. 

INSERT INTO availability 
(userid, days, opentime, closetime) 
VALUES 
(?, 'Monday', ?, ?),
(?, 'Tuesday', ?, ?),
(?, 'Wednesday', ?, ?),
(?, 'Thursday', ?, ?),
(?, 'Friday', ?, ?), 
(?, 'Saturday', ?, ?), 
(?, 'Sunday', ?, ?)
ON DUPLICATE KEY UPDATE
opentime = VALUES(opentime),
closetime = VALUES(closetime) ;

is my way correct? or is there any to do this?

[Barand]

That looks good - I see you found the VALUES() function needed for multiple updates

[thara]

@Barand,

 

Is there a way to use 'SELECT' with this?

[Barand]

In what way?

 

You can insert data into one table that is selected from another.

[thara]

No I mean, is there any way to get data after INSERTING or UPDATING with a single query...

[kicken]

No, there is not a way to INSERT/UPDATE data and SELECT the data back out from the same query. You'd have to do a separate select query after your insert/update query.

[thara]

No I mean, is there any way to get data after INSERTING or UPDATING with a single query...

 

Thank you.

[priyankagound]

Here is a solution which will surely help you...

This is the example which i tried..

If you add a unique index on (userid, days):

-- run this only once
ALTER TABLE availability
ADD UNIQUE INDEX userid_days_UQ -- just a name for the index
(userid, days) ;

then you can use the ON DUPLICATE KEY UPDATE syntax:

$q = " INSERT INTO availability
(userid, days, opentime, closetime)
VALUES
(?, 'Monday', ?, ?),
(?, 'Tuesday', ?, ?),
(?, 'Wednesday', ?, ?),
(?, 'Thursday', ?, ?),
(?, 'Friday', ?, ?),
(?, 'Saturday', ?, ?),
(?, 'Sunday', ?, ?)
ON DUPLICATE KEY UPDATE
opentime = VALUES(opentime),
closetime = VALUES(closetime) ; "; 

[mac_gyver]

@priyankagound if you look at post #7 in this thread, you will see that the OP already did that.

 

marking this thread as solved...