doumubi6784
doumubi6784
2017-11-13 17:42

SQL:使用条件一次插入或更新多行

  • php
  • mysql
  • sql

To understand what I'd like to achieve, here is my problem :

  • I have a list of "Place" in php array + sql table name "Place"
  • Each "Place" can have one or multiple "Place_Box"
  • Each "Place_Box" have (or not) some "Slot_Place_Box"
  • This "Slot_Place_Box" have field "date", "hour", "slot_available"
  • Now I have a list of "Date" + a list of "Hour" in some php arrays (some date have hour, other have not)
  • I have a "Type" var in php ("1", "2" or "3")

What I want to do :

  • I need to check if for each "Place", for each "date"/"hour" I already have a "Slot_Place_Box" in database :
  • If YES : I update "slot_available" -> current data - Type
  • If NO : I create a new "Slot_Place_Box" -> date + hour + slot_available = 3 - type
  • THEN : I have to get the last ID (the new I created or the one I updated) and create a new "Slot_config" data with this ID + an other ID I have in php var.

Is it possible to achieve this in ONE Script/Request, using only MySQL and php? I just want to have few repetion and avoid to make request in php foreach loop...

  • I saw this anwser to do the "update or insert" part : link
  • I saw this one for the "insert multiple row in one query" part : link

But I have doubt that I can combinate the two...I'd like to do something like this but I'm almost sure this is wrong and I will have to make at least one request by "Place" :

INSERT INTO Slot_Place_Box ( Date, Hour ) VALUES
    ( "2017-11-01", "23:00:00" )
ON DUPLICATE KEY UPDATE
    Date = "2017-11-01", Hour = "23:00:00"
LEFT JOIN "Place_Box" as PB ON PB.id_place_box = Slot_Place_Box.id_place_box
INNER JOIN "Place" as P ON P.id_place = PB.id_place

@id = SELECT LAST_INSERT_ID() or @id = (SELECT id FROM slot_place_box WHERE date = "2017-11-01" AND hour = "23:00:00"

INSERT INTO "Slot_config" (id_slot_pb, id_config) VALUES (@id, :id_config)

I hope my problem is clear enough to understand what I'd like to achieve, I'm beginner in SQL so if you just have some "logic" to help me make my script it would be very nice :)

EDIT 1 :

After one more day working on this, this is how I think I will do it :

  • I get every "id_place_box" according to my "Place" data
  • Then I will create an array with all the combinaison possible of "id_place_box" / "date" / "hour"
  • Then I will make a "SELECT" of all the "slot_place_box" where I can find a combination of id_place_box / date / hour -> I get "slot_available" + "id_slot_pb" value, then I UPDATE every result and keep the id's in an array.
  • I now can make an INSERT IGNORE new "slot_place_box" with all the combination "id/date/hour" and add every new id to my id's array.
  • Then I create a new "slot_config" foreach id in my array

I think it should works like this

EDIT 2 :

I'm not sure I can do what I wanted to achieve (insert or update multiple row at once) for the following reason :

  • I can't really UPDATE the "slot_available" field on "Slot_place_box" with some calcul, I need to get the current value, then find the new value according to my "type" value : I can't do

    INSERT INTO Slot_Place_Box ( id_place_box, Date, Hour ) VALUES
      ( :id_place_box_1, :date_1, :hour_1 ), 
      ( :id_place_box_2, :date_2, :hour_2 ), 
      ...
      ( :id_place_box_n, :date_n, :hour_n )n
    ON DUPLICATE KEY UPDATE slot_available = [some calcul]
    
  • The other problem is that I can't get all the id's I UPDATED or INSERTED if I do all in one request. After each update or insert I need to keep the id somewhere.

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答