Question 308734 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql update with crossreference not Having item

sorry i wrote in german… Here again…

I’m stuck and hope you can help me!

I have a mysql database with two tables: booking and article. They are linked via booking.Uid and article.booking_Uid. There can be several articles in each booking.

I want to change booking.status to "Ready for price" of all bookings if there is an article in this booking that has the value "xxx" in the tid field. So far so simple…
In addition, there may not be an article with price = 9999 in this booking.

This last point is where I fail!

Thank you in advance

UPDATE booking
SET status = 'Preis-fertig'
WHERE Uid IN (
    SELECT Uid
    FROM (
        SELECT b.Uid
        FROM booking b
        JOIN article a ON b.Uid = a.booking_Uid
        WHERE a.tid = 'xxx' AND a.price != 9999
    ) AS subquery
);

Second Part is wrong….

Tags:

2

Answers


  1. 0

    change booking.status to "Ready for price" of all bookings if there is an article in this booking that has the value "xxx" in the tid field.

    In addition, there may not be an article with price = 9999 in this booking.

    If these conditions must refer to the same row then:

    UPDATE booking b
JOIN article a ON b.Uid = a.booking_Uid
SET b.status = 'Preis-fertig'
WHERE a.tid = 'xxx' AND a.price != 9999

    If these conditions may refer to different rows then:

    UPDATE booking b
JOIN article a ON b.Uid = a.booking_Uid
LEFT JOIN article a1 ON b.Uid = a1.booking_Uid AND a1.price = 9999
SET b.status = 'Preis-fertig'
WHERE a.tid = 'xxx' 
  AND a1.booking_Uid IS NULL
    Login or Signup to reply.
  2. 0 
    UPDATE booking
SET status = 'Preis-fertig'
WHERE Uid IN (
    SELECT b.Uid
    FROM booking b
    INNER JOIN article a ON b.Uid = a.booking_Uid
    WHERE a.tid = 'xxx'
    AND b.Uid NOT IN (
        SELECT booking_Uid
        FROM article
        WHERE price = 9999
    )
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search