Error in running SQL Query in PHPmyadmin

Question

Table Schema:

Employee (empid, emp_name, age, DOB, designation, doj, basic_salary, salary_amt, medical_allowance, pension, GPFund, TA, House_allowance)
Monthly_salary (id, empid, year, month, total_salary)

Query:

UPDATE `monthly_salary` 
     SET `total_salary`=`employee`.'salary_amt' -`employee`.'medical_allownces' - `employee`.'pension' 
     WHERE `employee`.'empid'=`monthly_salary`.'emp_id'

I'm trying to run this Query in phpmyadmin but its not running and giving errors. Employees empid is used as foreign key in monthly-salary table.

Post your table schema

Narendrasingh Sisodia
– Narendrasingh Sisodia

2015-05-16 10:21:44 +00:00
Commented May 16, 2015 at 10:21 — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented May 16, 2015 at 10:21
@Uchiha see updates.

Khurram Shehzad
– Khurram Shehzad

2015-05-16 10:23:43 +00:00
Commented May 16, 2015 at 10:23 — Khurram Shehzad
– Khurram Shehzad, Commented May 16, 2015 at 10:23

Mad Dog Tannen · Accepted Answer · 2015-05-16 10:34:35Z

1

Remove the single qoutes '

Should looke like this

UPDATE `monthly_salary` salary
JOIN    `employee`      employee    ON employee.empid=salary.emp_id
SET     salary.total_salary=employee.salary_amt - employee.medical_allownces - employee.pension

I gave the tables alias names, makes it easier to read.

answered May 16, 2015 at 10:34

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hitesh Mundra · Accepted Answer · 2015-05-16 10:36:04Z

0

join in update is not used in your query.

UPDATE monthly_salary a
    JOIN employee b  ON  b.empid = a.emp_id
SET a.total_salary=b.salary_amt - b.medical_allownces - b.pension;

edited May 16, 2015 at 10:36

answered May 16, 2015 at 10:26

Hitesh Mundra

1,5731 gold badge10 silver badges13 bronze badges

5 Comments

Khurram Shehzad Over a year ago

following error is occurring: <br \> #1064 - 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 ''empid'=monthly_salary.'emp_id' SET total_salary=employee.'salary_amt' -`' at line 2

Hitesh Mundra Over a year ago

I you facing syntax error, becasue i can't test lack of db. you can use the link- stackoverflow.com/questions/15209414/mysql-update-join

Narendrasingh Sisodia Over a year ago

Remove those quotes from column name

Khurram Shehzad Over a year ago

@hitesh Thanks for helping.<br \> can u tell me one thing more please <br \> in database connection $con=mysql_connect("localhost", "root",""); root location is where ? were should i store my database in xamp folder?

Hitesh Mundra Over a year ago

@KhurramShehzad localhost is host where your mysql server is running. (localhost means your computer have ) root is user name, and "" contain password, which is currently blank. database is stored in xampp/mysql/data folder

Collectives™ on Stack Overflow

Error in running SQL Query in PHPmyadmin

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related