How can I set id_supply to the last entry/id in suply

Question

I've used This Stack for my query:

UPDATE breads
SET id_supply = (SELECT max(id) FROM supply)
WHERE id = (SELECT MAX(id) FROM breads)

I have added a new record into supply. Now I want my last bread to have the id_supply of the last supply.id

#1093 - Table 'breads' is specified twice, 
both as a target for 'UPDATE' and as a separate source for data

This seems correct to me. Are you getting an error? What's the problem? — JNevill, Oct 18 '17 at 13:35

score 1 · Answer 1 · answered Oct 18 '17 at 13:40

1

In MySQL, you cannot refer to the table being updated subsequently in the query (well, without a little hack).

Instead, use order by and limit:

UPDATE breads b
    SET b.id_supply = (SELECT max(s.id) FROM supply s)
    ORDER BY b.id DESC
    LIMIT 1;

answered Oct 18 '17 at 13:40

Gordon Linoff

1

@Halfacht . . . If that is the case, then the `id_supply` of the "last" row is already set to the maximum supply id. – Gordon Linoff Oct 19 '17 at 02:49

score 0 · Answer 2 · answered Oct 18 '17 at 15:04

If the answer given by @Gordon is not working, then I guess you should try a little hack he's talking about in his answer.

Although, Gordon's query works fine with my Test data, but if it's not working for you then try the following query:

UPDATE breads
SET id_supply = (SELECT max(id) FROM supply)
WHERE id = (SELECT MAX(br.id) FROM (select *from breads) br);

Click here for the Demo

Hope it helps!

Note: If this query won't work too, then you should add the Schema and Sample data into your question.

2 Answers2