MYSQL multiple queries

[labhijitp]

I have these two queries which i want to run together and not one by one. How can i do that? Or can it be modified to be one query.

UPDATE runquery.table1, runquery.table2 SET runquery.table1.`Column1` = (runquery.table2.`Col1`/runquery.table2.`Col2`*100) Where runquery.table2.`itemid` = 3 and runquery.table1.`Userids` = runquery.table2.`userid`;
UPDATE runquery.table1, runquery.table2 SET runquery.table1.`Column2` = (runquery.table2.`Col1`/runquery.table2.`Col2`*100) Where runquery.table2.`itemid` = 5 and runquery.table1.`Userids` = runquery.table2.`userid`;

[debasisdas]

try this, may work for you.

UPDATE runquery.table1 
SET runquery.table1.`Column1` = (select (runquery.table2.`Col1`/runquery.table2.`Col2`*100) from  runquery.table1 , runquery.table2 WHERE runquery.table2.`itemid` = 3 AND runquery.table1.`Userids` = runquery.table2.`userid`, runquery.table1.`Column2` = select (runquery.table2.`Col1`/runquery.table2.`Col2`*100) from  runquery.table1 , runquery.table2 WHERE runquery.table2.`itemid` = 5 AND runquery.table1.`Userids` = runquery.table2.`userid`;

[labhijitp]

try this, may work for you.

UPDATE runquery.table1 
SET runquery.table1.`Column1` = (select (runquery.table2.`Col1`/runquery.table2.`Col2`*100) from  runquery.table1 , runquery.table2 WHERE runquery.table2.`itemid` = 3 AND runquery.table1.`Userids` = runquery.table2.`userid`, runquery.table1.`Column2` = select (runquery.table2.`Col1`/runquery.table2.`Col2`*100) from  runquery.table1 , runquery.table2 WHERE runquery.table2.`itemid` = 5 AND runquery.table1.`Userids` = runquery.table2.`userid`;

Gives error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' runquery.table1.`Column2` = SELECT (runquery.table2.`Col1`/runquery.table2.`Col' at line 1

[labhijitp]

Database backup:

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.1.32-community


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema runquery
--

CREATE DATABASE IF NOT EXISTS runquery;
USE runquery;

--
-- Definition of table `table1`
--

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Userids` int(10) unsigned DEFAULT NULL,
  `Column1` int(10) unsigned DEFAULT NULL,
  `Column2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `table1`
--

/*!40000 ALTER TABLE `table1` DISABLE KEYS */;
INSERT INTO `table1` (`id`,`Userids`,`Column1`,`Column2`) VALUES 
 (1,1111,NULL,NULL),
 (2,1200,NULL,NULL),
 (3,1300,NULL,NULL);
/*!40000 ALTER TABLE `table1` ENABLE KEYS */;


--
-- Definition of table `table2`
--

DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `itemid` int(10) unsigned DEFAULT NULL,
  `Col1` int(10) unsigned DEFAULT NULL,
  `Col2` int(10) unsigned DEFAULT NULL,
  `userid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `table2`
--

/*!40000 ALTER TABLE `table2` DISABLE KEYS */;
INSERT INTO `table2` (`id`,`itemid`,`Col1`,`Col2`,`userid`) VALUES 
 (1,3,54,100,1111),
 (2,3,60,100,1200),
 (3,5,34,50,1300),
 (4,5,44,50,1111),
 (5,5,23,50,1200),
 (6,3,70,100,1300);
/*!40000 ALTER TABLE `table2` ENABLE KEYS */;




/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

[debasisdas]

UPDATE runquery.table1 
SET runquery.table1.Column1 = (SELECT (runquery.table2.Col1/runquery.table2.Col2*100) FROM  runquery.table1 , runquery.table2 WHERE runquery.table2.itemid = 3 AND runquery.table1.Userids = runquery.table2.userid

is this working ?

[labhijitp]

UPDATE runquery.table1 
SET runquery.table1.Column1 = (SELECT (runquery.table2.Col1/runquery.table2.Col2*100) FROM  runquery.table1 , runquery.table2 WHERE runquery.table2.itemid = 3 AND runquery.table1.Userids = runquery.table2.userid

is this working ?

You have opened '(' before SELECT but have not closed it anywhere. However, if i close the bracket at the end i see this error:

You can't specify target table 'table1' for update in FROM clause

And if i continue using your script i see this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[labhijitp]

UPDATE runquery.table1 SET runquery.table1.Column1 = (SELECT (runquery.table2.Col1/runquery.table2.Col2*100) FROM  runquery.table2 WHERE runquery.table2.itemid = 3 AND runquery.table1.Userids = runquery.table2.userid)

Works but only updates Column1 and not Column2.

[debasisdas]

repeat the same for other column also.

[labhijitp]

but that will make it two queries again..

[debasisdas]

use this.

UPDATE runquery.table1 SET runquery.table1.Column1 = (SELECT (runquery.table2.Col1/runquery.table2.Col2*100) FROM  runquery.table2 WHERE runquery.table2.itemid = 3 AND runquery.table1.Userids = runquery.table2.userid),
runquery.table1.Column2 = (SELECT (runquery.table2.Col1/runquery.table2.Col2*100) FROM  runquery.table2 WHERE runquery.table2.itemid = 5 AND runquery.table1.Userids = runquery.table2.userid)

[smantscheff]

Use two queries and bracket them into a transaction. That makes them from a logical and database point of view only one query in two chunks.
If you don't like that, use this inefficient solution:

UPDATE runquery.table1, runquery.table2 SET 
runquery.table1.`Column1` = if(itemid=3,(runquery.table2.`Col1`/runquery.table2.`Col2`*100),Column1),
runquery.table1.`Column2` = if(itemid=5,(runquery.table2.`Col1`/runquery.table2.`Col2`*100),Column2)
where runquery.table1.`Userids` = runquery.table2.`userid`;

[labhijitp]

This is working...Thanks Deb!

[labhijitp]

Use two queries and bracket them into a transaction. That makes them from a logical and database point of view only one query in two chunks.
If you don't like that, use this inefficient solution:

UPDATE runquery.table1, runquery.table2 SET 
runquery.table1.`Column1` = if(itemid=3,(runquery.table2.`Col1`/runquery.table2.`Col2`*100),Column1),
runquery.table1.`Column2` = if(itemid=5,(runquery.table2.`Col1`/runquery.table2.`Col2`*100),Column2)
where runquery.table1.`Userids` = runquery.table2.`userid`;

@smantscheff
This one updates only three cells out of six. I would go with Deb's solution. Thanks!