CODEWITHSUNDEEP
mysql
Paolo Rossi
Paolo Rossi

Reputation: 2510

MYSQL update into select 3 tables

I use this query (insert into select 3 tables) to insert my rows in the mysql table. 

"INSERT INTO test (catid_1, descat_1, catid_2, descat_2, id_user, user)
 SELECT '$_POST[cat_1]',t1.desc AS descat_1, '$_POST[cat_2]', t2.desc AS descat_2, 
 $_POST[id_user]',t3.user FROM t1, t2, t3 
 WHERE t1.idcat_1='$_POST[cat_1]' and t2.idcat_2='$_POST[cat_2]' 
 and t3.id_user='$_POST[id_user]'";

Now I'd like to use the same logic to UPDATE my rows (update into select 3 table) in the mysql table.

tables structure

t1
`idcat_1` int(11) NOT NULL AUTO_INCREMENT,
`desc` varchar(100) NOT NULL,
PRIMARY KEY (`idcat_1`)

t2
`idcat_2` int(11) NOT NULL AUTO_INCREMENT,
`idcat_1` int(11) NOT NULL,
`desc` varchar(100) NOT NULL,
PRIMARY KEY (`idcat_2`)

t3
`id_user` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(40) NOT NULL,
PRIMARY KEY (`id_user`)

Is it possible to do?

Thanks

Upvotes: 2

Views: 1967

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Like this:

UPDATE test AS t
INNER JOIN t1 ON -- join conditon
INNER JOIN t2 ON ...
INNER JOIN t3 ON ... 
SET t.catid_1 = '$_POST[cat_1]',
    t.descat_1 = t1.desc,
    ....
 WHERE t1.idcat_1='$_POST[cat_1]' 
   and t2.idcat_2='$_POST[cat_2]' 
   and t3.id_user='$_POST[id_user]'

It is not clear how the four tables are joined, you will need to supply the join condition for each JOIN.

Update 1

From the tables' structures that you just posted in your updated question, it seems like neither of the three tables test, t1 nor t2 related to the table t3 by any keys. In this key you didn't need to join with this table t3 and only join the tables test, t1 and t2. Assuming that:

  • test relates to t1 with test.catid_1 = t1.idcat_1.
  • t1 relates to t2 with t1.idcat_1 = t2.idcat_1.

Like this:

UPDATE test AS t
INNER JOIN t1 ON t.catid_1 = t1.idcat_1
INNER JOIN t2 ON t1.idcat_1 = t2.idcat_1
SET t.catid_1 = '$_POST[cat_1]',
    t.descat_1 = t1.desc,
    ....
 WHERE t1.idcat_1='$_POST[cat_1]' 
   and t2.idcat_2='$_POST[cat_2]'

Upvotes: 4

Related Questions