Showing posts with label TempTables. Show all posts
Showing posts with label TempTables. Show all posts

Jan 30, 2016

Updating a temporary table from another table by matching column values.

Hello,

Im new to this Forum and I need to update a temporary table based on matching values from another table.For example i have two tables

table1                                                    

month(varchar)  sales(int)                        

1                              -

2                               -
3                               -
4                               -
5                               -
  table2
month(varchar)      sales(int)
2                              100
4                               50
5                               100
In this example i need to update  the  table1 sales column with table2 sales column by matching the month value of both the tables..


ans is 
UPDATE t1 SET t1.sales=t2.sales
FROM table1 t1 INNER JOIN table2 t2 ON t1.MONTH=t2.MONTH


You can simply try the left outer join:

create table #table1 (month varchar(10), sales int)
insert into #table1 values ('1',null)
insert into #table1 values ('2',null)
insert into #table1 values ('3',null)
insert into #table1 values ('4',null)
insert into #table1 values ('5',null)
create table #table2 (month varchar(10), sales int)
insert into #table2 values ('2',100)
insert into #table2 values ('4',50)
insert into #table2 values ('5',100)
select a.[month], b.sales from #table1 a left outer join #table2 b
on a.[month]=b.[month]
drop table #table1
drop table #table2

If you want to update table1 

Please try the below query
update a set a.[sales]=b.sales from #table1 a 
left outer join #table2 b on a.[month]=b.[month] 
select * from #table1 

Dec 15, 2010

How to Using Temporary Tables in Mysql

*
mysql> INSERT INTO tmp

    ->    SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
    ->    FROM StudentExam
    ->       INNER JOIN Student
    ->       ON StudentExam.StudentID = Student.StudentID
    ->    GROUP BY Student.Name;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+-------------+-------------+
| StudentName | AverageMark |
+-------------+-------------+
| Cory But    |          44 |
| Joe Wang     |          64 |
+-------------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT StudentName, AverageMark FROM tmp WHERE AverageMark < 50;
+-------------+-------------+
| StudentName | AverageMark |
+-------------+-------------+
| Cory But    |          44 |
+-------------+-------------+
1 row in set (0.00 sec)


*/

/* Create Student and StudentExam TABLE  */
Drop TABLE Student;
Drop TABLE StudentExam;

CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;

CREATE TABLE StudentExam (
   StudentID  INT NOT NULL,
   Mark       INT,
   Comments   VARCHAR(255),

   CONSTRAINT FK_Student FOREIGN KEY (StudentID)
              REFERENCES Student(StudentID)

)TYPE = InnoDB;



/* Insert Data*/
INSERT INTO Student (StudentID,Name) VALUES (1,'John Jones');
INSERT INTO Student (StudentID,Name) VALUES (2,'Gary Burton');
INSERT INTO Student (StudentID,Name) VALUES (3,'Emily Scarlett');

INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,55,'Java');
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,73,'C#');
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (2,44,'JavaScript');

/* Using Temporary Tables */  
CREATE TEMPORARY TABLE tmp (StudentName VARCHAR(50), AverageMark INT);

INSERT INTO tmp
   SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
   FROM StudentExam
      INNER JOIN Student
      ON StudentExam.StudentID = Student.StudentID
   GROUP BY Student.Name;

select * from tmp;


SELECT StudentName, AverageMark FROM tmp WHERE AverageMark < 50;
SELECT StudentName, AverageMark FROM tmp WHERE AverageMark > 70;