SELECT UPDATE if the row does not exist.

Dev Shed Forums Sponsor:

December 12th, 2012, 08:06 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,604

Time spent in forums: 2 Weeks 5 Days 2 h 24 m 41 sec
Reputation Power: 71

SELECT UPDATE if the row does not exist.

Hello I need help to finish a query. I have done most of it and it is working fine, I just need to finish it.

Basically it is a "SELECT, if record not exist, UPDATE" query. I made some visual to make it very easy to understand. Basically if the values for ALLOTMENTS_DAY_DATE is NULL, I want to insert the required values. I have written the select part of the query and it works fine. I am successfully retrieving the values I need to insert (in case necessary). This is the select query

Code:

 SELECT packages.id              AS PACKAGE_ID,
       packages.hotel_id        AS HOTEL_ID,
       packages.room_id         AS ROOM_ID,
       annual_calendar.day_date AS DAY_DATE,
       allotments_new.day_date  AS ALLOTMENTS_DAY_DATE,
       packages.low_min_nights  AS PACKAGE_LOW_MIN_NIGHTS,
       packages.low             AS PACKAGE_LOW_RATE,
       hotels.beta_supplier     AS SUPPLIER_ID

FROM   packages

       INNER JOIN hotels
               ON hotels.hotel_id = packages.hotel_id
                  AND hotels.status = 'active'


       INNER JOIN hotelrooms
               ON hotelrooms.room_id = packages.room_id
                  AND hotelrooms.status = 'active'


       LEFT OUTER JOIN calendars_new
                    ON calendars_new.hotel_id = hotels.hotel_id
                       AND calendars_new.day_date <= :next_year
                       AND calendars_new.day_date >= :today


       LEFT OUTER JOIN allotments_new
                    ON allotments_new.package_id = packages.id
                       AND allotments_new.day_date <= :next_year
                       AND allotments_new.day_date >= :today
                       AND calendars_new.day_date = allotments_new.day_date


       LEFT OUTER JOIN annual_calendar
                    ON annual_calendar.day_date = calendars_new.day_date
                       AND annual_calendar.day_date <= :next_year
                       AND annual_calendar.day_date >= :today

WHERE  packages.status = 'active'

and this is the insert query I need to integrate:

Code:

 INSERT INTO allotments_new
            (package_id,
             room_id,
             hotel_id,
             date_added,
             day_date,
             day_rate,
             number_of_rooms,
             status,
             supplier_id,
             day_of_the_week)
VALUES      ('".$row['PACKAGE_ID']."',
             '".$row['ROOM_ID']."',
             '".$row['HOTEL_ID']."',
             '".date('Y-m-d')."',
             '$row['DAY_DATE']',
             '$row['PACKAGE_LOW_RATE']',
             0,
             'b',
             '".$row['SUPPLIER_ID']."',
             '".strtolower(date('l',strtotime($row['DAY_DATE']) ))."' )

Many thanks.

December 12th, 2012, 09:27 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,717

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 7 h 29 m 55 sec
Reputation Power: 8969

Send a message via Google Talk to requinix

First a question. Can you write the SELECT so that it pulls the data you want to insert? All the columns and all the values exactly as you want it to go into the table.
* For literal values you can just SELECT "literal string", 123
* NOW() will get you the current date/time
* LOWER() and DATE_FORMAT() can take the place of the strtolower() and date()+strtotime() you have now

Once you have the SELECT as you want, turn it into an INSERT...SELECT

Code:

INSERT INTO table (fields) SELECT ...

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

December 12th, 2012, 10:22 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,604

Time spent in forums: 2 Weeks 5 Days 2 h 24 m 41 sec
Reputation Power: 71

Quote:

Originally Posted by requinix

First a question. Can you write the SELECT so that it pulls the data you want to insert? All the columns and all the values exactly as you want it to go into the table.

Hello, thank you. Yes. The query above does that.

Quote:

Originally Posted by requinix

* For literal values you can just SELECT "literal string", 123
* NOW() will get you the current date/time
* LOWER() and DATE_FORMAT() can take the place of the strtolower() and date()+strtotime() you have now
Once you have the SELECT as you want, turn it into an INSERT...SELECT

Code:

INSERT INTO table (fields) SELECT ...

I retrieve all the values. I understand the concept, writing it down as a query is very difficult. Specially the if part.

Last edited by zxcvbnm : December 12th, 2012 at 10:35 PM.

February 7th, 2013, 01:13 AM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,604

Time spent in forums: 2 Weeks 5 Days 2 h 24 m 41 sec
Reputation Power: 71

Quote:

Originally Posted by requinix

Code:

INSERT INTO table (fields) SELECT ...

How can I insert? Where in the query? I made a
visual help. If you scroll down from row 1860 you see the yellow text saying record not found. Those are the records I need to insert.

I think I have to write the query like this below but I need some help to write this query

Code:

INSERT INTO table () VALUES ()
WHERE SELECT columns FROM table WHERE id = NULL

February 7th, 2013, 01:47 AM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,717

Time spent in forums: 5 Months 1 Week 4 Days 7 h 29 m 55 sec
Reputation Power: 8969

copy/paste from a PM

Add a WHERE with allotments_new.day_date IS NULL: that should get you only the yellow rows. Then make sure you're selecting only the columns whose data you need (package_id, room_id, ... supplier_id, day_of_the_week) and stick an "INSERT INTO allotments_new (columns...)" in front of the SELECT.

So I think something like

Code:

INSERT INTO allotments_new (package_id, room_id, hotel_id, date_added, day_date,
	day_rate, number_of_rooms, status, supplier_id, day_of_the_week)
SELECT
	packages.id,
	packages.room_id,
	packages.hotel_id,
	NOW(),
	annual_calendar.day_date,
	/* number_of_rooms */,
	/* status */,
	hotels.beta_supplier,
	/* day_of_the_week */
FROM packages
INNER JOIN hotels ON
	hotels.hotel_id = packages.hotel_id
	AND hotels.status = 'active'
INNER JOIN hotelrooms ON
	hotelrooms.room_id = packages.room_id
	AND hotelrooms.status = 'active'
LEFT OUTER JOIN calendars_new ON
	calendars_new.hotel_id = hotels.hotel_id
	AND calendars_new.day_date <= '2014-02-07'
	AND calendars_new.day_date >= '2013-02-07'
LEFT OUTER JOIN allotments_new ON
	allotments_new.package_id = packages.id
	AND allotments_new.day_date <= '2014-02-07'
	AND allotments_new.day_date >= '2013-02-07'
	AND calendars_new.day_date = allotments_new.day_date
LEFT OUTER JOIN annual_calendar ON
	annual_calendar.day_date = calendars_new.day_date
	AND annual_calendar.day_date <= '2014-02-07'
	AND annual_calendar.day_date >= '2013-02-07'
WHERE
	packages.status = 'active'
	AND packages.hotel_id = 4
	AND allotments_new.day_date IS NULL

Make sure you're not getting duplicate rows back: cut out any table references you don't need, and if you can't think of anything else to do make it a SELECT DISTINCT (the performance will drop but it'll work).

Also, you can use BETWEEN...AND in place of a >= and <=, like

Code:

calendars_new.day_date BETWEEN '2013-02-07' AND '2014-02-07'

Side thought: if number_of_rooms comes from a COUNT on the hotelrooms table you can add in a GROUP BY just like normal.

SELECT UPDATE if the row does not exist.

Developer Shed Advertisers and Affiliates