MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:ajitdixit Date:November 27 2002 5:04am
Subject:re: re: Multi-Table Updates
View as plain text  
Hi , 

Result for Show Grants is as under 

SQL result

Host: localhost

SQL-query: SHOW GRANTS FOR aldixit@localhost;
Grants for aldixit@localhost
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'aldixit'@'localhost'
IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxx'
GRANT SELECT ON `mysql`.* TO 'aldixit'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `samir`.* TO 'aldixit'@'localhost' WITH GRANT OPTION


Table Strecture areas in samir database

Database samir  - table areas  running on Shreya Intranet

areas

Field Type Null Default Links to Comments
acd  varchar(5) No  0     
aname  varchar(30) No       
depot  char(3) No  0     
repseq  int(2) No  0     
abbr  char(3) No       
state  char(2) No       
noreps  float(5,2) No  0.00     


 Indexes :
Keyname Type Cardinality Field
PRIMARY PRIMARY 365 acd
depot INDEX None depot
repseq INDEX None repseq


 Space usage :
Type Usage
Data 11,544 Bytes
Index 15,360 Bytes
Overhead 448 Bytes
Effective 26,456 Bytes
Total 26,904 Bytes
   Row Statistic :
Statements Value
Format dynamic
Rows 365
Row length ø 30
Row size  ø 74 Bytes

SQL For Areas


CREATE TABLE areas (
  acd varchar(5) NOT NULL default '0',
  aname varchar(30) NOT NULL default '',
  depot char(3) NOT NULL default '0',
  repseq int(2) NOT NULL default '0',
  abbr char(3) NOT NULL default '',
  state char(2) NOT NULL default '',
  noreps float(5,2) NOT NULL default '0.00',
  PRIMARY KEY  (acd),
  KEY depot (depot),
  KEY repseq (repseq)
) TYPE=MyISAM;

    
Table strecture for Stockists table in samir database


Database samir  - table Stockists  running on Shreya Intranet
Stockists
Field Type Null Default Links to Comments
cucode  varchar(7) No      Stockist Code 
d_code  char(3) No    depots -> depotid  Depot 
cu_code  varchar(4) No      Stockist 
cu_nm  varchar(40) No      Stockist Name 
area  varchar(5) Yes  NULL  areas -> acd  Area 
a_nm  varchar(30) No      Area Name 
d_name  varchar(15) Yes  NULL    Depot Name 
stkfullname  varchar(204) Yes  NULL    Full Name 
repseq  int(3) Yes  NULL    Rep . Seq 


 Indexes :
Keyname Type Cardinality Field
repseq INDEX 4 repseq
area INDEX 1349 area
cucode INDEX 5396 cucode


 Space usage :
Type Usage
Data 708,888 Bytes
Index 191,488 Bytes
Total 900,376 Bytes
   Row Statistic :
Statements Value
Format dynamic
Rows 5,396
Row length ø 131
Row size  ø 167 Bytes

SQL For Stockists
CREATE TABLE Stockists (
  cucode varchar(7) NOT NULL default '',
  d_code char(3) NOT NULL default '',
  cu_code varchar(4) NOT NULL default '',
  cu_nm varchar(40) NOT NULL default '',
  area varchar(5) default NULL,
  a_nm varchar(30) NOT NULL default '',
  d_name varchar(15) default NULL,
  stkfullname varchar(204) default NULL,
  repseq int(3) default NULL,
  KEY repseq (repseq),
  KEY area (area),
  KEY cucode (cucode)
) TYPE=MyISAM;

    
Updating Only areas:
Affected rows: 1
SQL-query : [Edit] [Create PHP Code]
update areas set aname = 'BALAGHAT' where acd = '78193'
Updation Succesful


Updating Only Stockist

Affected rows: 1
SQL-query : [Edit] [Create PHP Code]
update Stockists set a_nm = 'BALAGHAT' WHERE area = '78193'
Updation Succesful


Multi-Table Update with user aldixit

SQL-query : 

update Stockists, areas set a_nm = aname WHERE area = acd

MySQL said:

update command denied to user: 'aldixit@localhost' for table 'areas'



Multi-Table Update with root user

Affected rows: 1
SQL-query : [Edit] [Create PHP Code]
update Stockists, areas set a_nm = aname WHERE area = acd
Updation Succesful



I have extensively tested this with different users but the query results are same

Regards

Ajit Dixit  

-------------------------------------------------
This mail sent through http://shreya.co.in/
Thread
Multi-Table Updatesajitdixit26 Nov
  • re: Multi-Table UpdatesEgor Egorov26 Nov
    • re: Multi-Table Updatesajitdixit26 Nov
      • re: re: Multi-Table UpdatesEgor Egorov26 Nov
        • re: re: Multi-Table Updatesajitdixit27 Nov
          • re: re: re: Multi-Table UpdatesEgor Egorov27 Nov
            • re: re: re: Multi-Table Updatesajitdixit27 Nov
              • re: re: re: re: Multi-Table UpdatesEgor Egorov2 Dec