Simple SQL Query

Databases
1

Hi
I am looking to update a table via phpmyadmin


update mos_akocomment set contentid = '20' where contentid = '31';
update mos_akocomment set contentid  = '73' where contentid = '93';
update mos_akocomment set contentid  = '199' where contentid = '200';

now these statements execute fine, however can someone tell me that what will be the format of this query If I want to write it in the different way so that I call update mos_akocomment only once (i.e. in the format in which you have the phpmyadmin exports)

like


INSERT INTO `mos_akocomment` (`id`, `contentid`, `ip`, `name`, `email`, `comment`, `date`) VALUES
(1, 1, '0', 'Guest', 'Guest', 'cool ', '2010-03-19 15:00:25'),
(2, 1, '0', 'Guest', 'Guest', 'hello', '2010-04-07 16:40:06'),

I have tried something like


update mos_akocomment set 
( contentid = '20' where contentid = '31'),
( contentid  = '73' where contentid = '93'),
( contentid  = '199' where contentid = '200');

but this does not work

2

two things to note,

1 you don’t need to quote numeric values as you keep doing (unless they are varchar/char field),

2 you should have an else condition otherwise if you have a value in the table for contentid that isn’t in that list, let’s say 96 for example, that row will have contentid set to NULL for that row. Your update should be:


UPDATE mos_akocomment
SET contentid= CASE
WHEN contentid = 20 THEN 31
WHEN contentid = 73 THEN 93
WHEN contentid = 199 THEN 200
WHEN contentid = 165 THEN 204
WHEN contentid = 79 THEN63
WHEN contentid = 71THEN 82
WHEN contentid = 6 THEN 17
WHEN contentid = 62 THEN 80
WHEN contentid = 132 THEN 126
[B]ELSE contentid[/B]
END

which says basically if the contentid in a row doesn’t match one of the conditions, leave it alone or update it to the existing value.

3

yes, It worked
1st I had run the sample posted above and it had zeroed all conetedid rows that did not match
it worked when I ran the complete query, there was no case of the condition not matching

4

Thanks
that had to add the contentid= CASE line

UPDATE mos_akocomment
SET contentid= CASE
WHEN contentid = ‘20’ THEN ‘31’
WHEN contentid = ‘73’ THEN ‘93’
WHEN contentid = ‘199’ THEN ‘200’
WHEN contentid = ‘165’ THEN ‘204’
WHEN contentid = ‘79’ THEN’63’
WHEN contentid = '71’THEN ‘82’
WHEN contentid = ‘6’ THEN ‘17’
WHEN contentid = ‘62’ THEN ‘80’
WHEN contentid = ‘132’ THEN ‘126’
END

finally I migrated 2K comments from a 6 year old Mambo to shinny new wordpress :slight_smile:

5

that worked???

:eek:

you know what a CASE expression produces if none of the WHEN conditions is true? the default is ELSE NULL

if you ran that query without a WHERE clause, you just updated every contentid that wasn’t listed in the CASE to NULL

6 

UPDATE
  mos_akocomment
SET
  CASE 
    WHEN contentid=31 then 20
    WHEN contentid=93 then 73
    WHEN contentid =200 then 199
  END

note: untested

7

guelphdad, your SET clause is missing the equal sign