CODEWITHSUNDEEP
mysqljoin
marczak
marczak

Reputation: 489

MySql update with JOIN himself

HI I have query which selected me all rows which have primary key (id) = 1 or have foreign key = 1. This is join himself.

SELECT :

SELECT f2.wz AS wz FROM d7x6r_magazyn_faktura_zakupowa f

                        LEFT JOIN (SELECT id,id_glowna, wz FROM d7x6r_magazyn_faktura_zakupowa   )AS f2
                        ON(((f2.id = f.id_glowna OR f2.id = f.id OR f2.id_glowna = f.id OR f2.id_glowna = f.id_glowna)
                        AND f2.id_glowna <> 0)
                        OR ( f2.id = f.id  OR f2.id = f.id_glowna))
                        WHERE f.id = 1 OR f2.id_glowna  = 1;

Now I need to update cell wz to 0 in all rows. I try using : 

UPDATE  d7x6r_magazyn_faktura_zakupowa f

  LEFT JOIN (SELECT id,id_glowna, wz FROM d7x6r_magazyn_faktura_zakupowa   )AS f2
                ON(((f2.id = f.id_glowna OR f2.id = f.id OR f2.id_glowna = f.id OR f2.id_glowna = f.id_glowna)
                AND f2.id_glowna <> 0)
                OR ( f2.id = f.id  OR f2.id = f.id_glowna))

SET f2.wz = 0
WHERE f.id = 1 OR f2.id_glowna  = 1;

But it's not working because f2.wz can't be updated.

Upvotes: 0

Views: 102

Answers (3)

Matheno
Matheno

Reputation: 4152

You can do joins on update/insert/delete, but not to update records in those joined tables.

You can do this using a trigger though

DELIMITER $$

CREATE TRIGGER au_table1_each AFTER UPDATE ON table1 FOR EACH ROW
BEGIN
  UPDATE table2 SET fieldY = new.fieldX WHERE ...;
END $$

DELIMITER ;

The trigger will fire on each update and update table2 using some of the new data in table1. There are triggers BEFORE and AFTER an action, which can be UPDATE, INSERT or DELETE

See: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html and: http://www.databasedesign-resource.com/mysql-triggers.html

Thanks to: mysql update, insert, delete with joins

Upvotes: 1

Kickstart
Kickstart

Reputation: 21533

You do not appear to need the subselect, and could just use a JOIN:-

UPDATE  d7x6r_magazyn_faktura_zakupowa f
LEFT JOIN d7x6r_magazyn_faktura_zakupowa f2
ON(((f2.id = f.id_glowna OR f2.id = f.id OR f2.id_glowna = f.id OR f2.id_glowna = f.id_glowna)
AND f2.id_glowna <> 0)
OR ( f2.id = f.id  OR f2.id = f.id_glowna))
SET f2.wz = 0
WHERE f.id = 1 OR f2.id_glowna  = 1;

However you have a LEFT JOIN but are updating F2 which is the row that might not be there so an INNER JOIN might be better

Upvotes: 1

jaczes
jaczes

Reputation: 1404

You have to create TMP table, also LEFT JOIN shouldn't be there

Upvotes: 0

Related Questions