pure MySQL loop to update multiple rows

Question

I want to update multiple rows based on a SELECT sql query.

I want to do it ALL IN AN SQL SHELL!

Here is my select:

SELECT @myid := id, @mytitle := title
FROM event 
WHERE pid>0 GROUP BY title
ORDER BY start;

Then, I want to do an update with this pseudocode:

foreach($mytitle as $t)
BEGIN
    UPDATE event
    SET pid=$myid
    WHERE title=$t;
END

But I don't know how to ake a loop in SQL.

Maybe there's a way to make it in a single sql query?

I DON'T WANT ANY PHP!!! ONLY SQL SHELL CODE!!!

I don't think you understand very well how SQL works. It's not clear from your question what change you are trying to make to your database. Could you please explain in English what changes you want to result from this "SQL loop"? That way, people will be able to explain what you need to do to effect those changes. — Hammerite
– Hammerite, Commented Oct 13, 2011 at 16:47
It's been a while since I've used MySQL, but your select query will get an error in most SQL dialects. id is neither an aggregate function nor is it included in the group-by. What are you trying to do? Are you expecting only one id for any given title? If you are trying to pick one of the id's associated with a title, which one? — Jay
– Jay, Commented Oct 13, 2011 at 16:51
I have a bug with my pids. So I want to update every rows with a pid with the id of the first occurence of an event. Start is a timestamp — Marm
– Marm, Commented Oct 13, 2011 at 16:52

Hammerite · Accepted Answer · 2011-10-13 17:02:24Z

7

I want to update every rows with a pid with the id of the first occurence of an event. Start is a timestamp

I think this should do what you want, but if it doesn't (I'm not sure about joining a subquery in an UPDATE query) then you can use a temporary table instead.

UPDATE
    event
    JOIN (
        SELECT
            MIN(pid) AS minPID,
            title
        FROM
            event
        WHERE
            pid > 0
        GROUP BY
            title
    ) AS findPIDsQuery ON event.title = findPIDsQuery.title
SET
    event.pid = findPIDsQuery.minPID

answered Oct 13, 2011 at 17:02

Hammerite

22.5k6 gold badges76 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nicholas Carey Over a year ago

SQL Server allows joins in an UPDATE statement. I'm pretty sure that that's not ISO-compliant SQL, though. It is, however, a Very Useful Feature. You can get the same effect with correlated subqueries (see my answer above).

Marm Over a year ago

it doesn't works, Unknown column 'findPIDsQuery.title' in 'on clause'

user752746 Over a year ago

Saved my day! This worked perfectly for me, thank you so much!

Nicholas Carey · Accepted Answer · 2011-10-13 17:03:34Z

0

Pure SQL doesn't really have "loops", per se: it's a set-based descriptive language. I believe the following update will do what you want (though your problem statements leaves much to be desired—we know nothing about the underlying schema).

update event t
set pid = ( select min(id)
            from event x
            where x.title = t.title
              and x.pid > 0
            group by x.title
            having count(*) > 1
          )

Cheers!

answered Oct 13, 2011 at 17:03

Nicholas Carey

75.1k16 gold badges96 silver badges138 bronze badges

2 Comments

Marm Over a year ago

It doesn't works, You can't specify target table 't' for update in FROM clause

Nicholas Carey Over a year ago

You might want to try consulting the mySql manual (dev.mysql.com/doc/refman/5.0/en/update.html). Or get rid of the table alias and qualify the column names with the table name instead of the alias.

Collectives™ on Stack Overflow

pure MySQL loop to update multiple rows

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related