swap column values

[Destramic]

hey guys i've tried to run this query on my database but it only partially works...the images paths will change but the values for cover won't budge

 

table

---------------------

item_id

path   - varchar

cover - int(1)

--------------------

 

cover will be 1 or 0 depending if the image is the main image.

 

i can see anything i'm doing wrong here...can someone please shine some light on why both path and cover values are not swapping.

 

here is my sql:

UPDATE item_images im1, item_images im2 
SET im1.cover = im2.cover, 
    im2.cover = im1.cover, 
    im1.path = im2.path, 
    im2.path = im1.path
WHERE im1.item_id = im2.item_id
AND im1.path = '1447700522.jpg'
AND im2.path = '1447700481.jpg'
AND im1.item_id = 58

thank you

[Barand]

mysql> SELECT * FROM item_images;
+----------------+---------+-------+---------+
| item_images_id | item_id | cover | path    |
+----------------+---------+-------+---------+
|              1 |      58 |     1 | 111.jpg |
|              2 |      58 |     0 | 222.jpg |
+----------------+---------+-------+---------+

Then your query (the names have been changed to protect the innocent)

UPDATE item_images im1, item_images im2 
SET im1.cover = im2.cover, 
    im2.cover = im1.cover, 
    im1.path = im2.path, 
    im2.path = im1.path
WHERE im1.item_id = im2.item_id
AND im1.path = '222.jpg'
AND im2.path = '111.jpg'
AND im1.item_id = 58;

gives

mysql> SELECT * FROM item_images;
+----------------+---------+-------+---------+
| item_images_id | item_id | cover | path    |
+----------------+---------+-------+---------+
|              1 |      58 |     0 | 222.jpg |
|              2 |      58 |     1 | 111.jpg |
+----------------+---------+-------+---------+

which looks OK to me

 

???

[Destramic]

Wasn't changing cover values on mine....I'll have another play when I'm back home...thanks for your reply, I'll let you know how it goes

[Destramic]

yeah cover column dosn't move....here is results upon select before sql.

 

 

image_id       item_id         path                                  cover

---------------------------------------------------------------------------

13                   58                1447700481.jpg                 1

14                   58                1447700522.jpg                 0

---------------------------------------------------------------------------

 

after sql execution

 

image_id       item_id         path                                  cover

---------------------------------------------------------------------------

13                   58               1447700522.jpg                 0

14                   58               1447700481.jpg                 1

---------------------------------------------------------------------------

 

here is the table i'm using

CREATE TABLE `item_images` (
	`image_id` INT(11) NOT NULL AUTO_INCREMENT,
	`item_id` INT(11) NOT NULL,
	`path` VARCHAR(130) NOT NULL,
	`cover` INT(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`image_id`),
	UNIQUE INDEX `item_images_image_id_UNIQUE` (`image_id`),
	INDEX `item_images_item_id_INDEX` (`item_id`),
	CONSTRAINT `item_images_item_id_FOREIGN_KEY` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

somethings not right although it seems it should be...be good to know what's up here.

 

thank you

[Barand]

Look again!

 

 

before: ID: 14     Cover:1
after:  ID: 14     Cover:0

Both the image and cover are changed.

 

If your objective is to switch which is the main image, just change cover and not the image name (or vice versa)

[Destramic]

Look again!

 

 

before: ID: 14     Cover:1
after:  ID: 14     Cover:0

Both the image and cover are changed.

 

If your objective is to switch which is the main image, just change cover and not the image name (or vice versa)

 

 

what a plonker i am...i took away setting the cover and it worked as i wanted it to

UPDATE item_images im1, 
       item_images im2 
SET im1.path = im2.path,  
    im2.path = im1.path 
WHERE im1.item_id = im2.item_id
AND im1.path = :cover_image_path
AND im2.path = :image_path
AND im1.item_id = :item_id

sorry for being a fool! haha...thank you for your help barand