Solved

SQL Query help!

Posted on 2006-07-10
22
239 Views
Last Modified: 2008-03-04
Hi, im able to get half my query to work, but am having issues with other part. I'm able to increase salary of workers but when i only want to search by one job code i get error after error.


This works
//
UPDATE Employee
SET salary= Salary2 * 1.10 /*100% + 10% salary increase*/
(SELECT salary2
FROM Employee)
//
This does not
//
UPDATE Employee
Where Job_title_code='Sales Workers'
SET salary= Salary2 * 1.10 /*100% + 10% salary increase*/
(SELECT salary2
FROM Employee)
//
0
Comment
Question by:steadfast9661
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +2
22 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17070912
UPDATE Employee
SET salary= Salary2 * 1.10 /*100% + 10% salary increase*/
(SELECT salary2
FROM Employee)
Where Job_title_code='Sales Workers'
0
 
LVL 9

Accepted Solution

by:
riaancornelius earned 64 total points
ID: 17070914
UPDATE Employee
SET salary= Salary2 * 1.10 /*100% + 10% salary increase*/
(SELECT salary2
FROM Employee)
Where Job_title_code='Sales Workers'
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17070916
damn... Too slow again :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17070918
Hi steadfast9661,
your where clause is in the wrong place....

its

UPDATE  <tablename>
  Set .....
 <from  tables ....>  -- Optional MS SQL Server Extension
  Where ....



Cheers!
0
 

Author Comment

by:steadfast9661
ID: 17070943
Hi all, thanks for thre replies. I'm still getting the same error as i tryed the sugested methods as well, perhaps my problem is else where....


Error message

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Where'.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 62 total points
ID: 17070964

/*100% + 10% salary increase*/


UPDATE Employee
SET salary= Salary2 * 1.10
Where Job_title_code='Sales Workers'




0
 

Author Comment

by:steadfast9661
ID: 17070980
Lowfastspeed, ty very much.


That doesnt give me an error and it shows that 4 rows were affected, however there are no results shown under grid....


Any ideas?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 62 total points
ID: 17070991

UPDATE Employee
SET salary= Salary2 * 1.10 /*100% + 10% salary increase*/
Where Job_title_code='Sales Workers'
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17070994
This should work

UPDATE Employee
SET salary= Salary2 * 1.10
Where Job_title_code='Sales Workers'
go


UPDATE Employee
Where Job_title_code='Sales Workers'     <----------------- where clause should always appear after set statement
SET salary= Salary2 * 1.10 /*100% + 10% salary increase*/
(SELECT salary2    <-------- no need if you are doing update on the same table
FROM Employee)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17070996
>That doesnt give me an error and it shows that 4 rows were affected, however there are no results shown under grid....
well, if you only want to SEE that changes, don't use UPDATE, but SELECT, resp use SELECT after the update to see the changes


0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 62 total points
ID: 17071003
>>That doesnt give me an error and it shows that 4 rows were affected, however there are no results shown under >>grid....

You are using Enterprise manager for updates bad practice use query analyzer and no data will be shown yes because you are updating the records not displaying them it will give you msg about how many records are updated only.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17071018
/*100% + 10% salary increase*/
UPDATE Employee
SET salary= Salary2 * 1.10
Where Job_title_code='Sales Workers'
SELECT salary2
FROM Employee
Where Job_title_code='Sales Workers'
0
 

Author Comment

by:steadfast9661
ID: 17071030
is there a way for me to accept multiple answers as EVERYONE was helpful....
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17071179
yes, but you have to do that before you accept any answer.

If you want you can ask the page editor to reopen the question, then click on the 'Split points' link on the bottom of the page.
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17071437
>> question reopened
>> angelIII, page editor
I didn't know you were page editor :) Thanks.
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17071523
>> didn't you read the latest EE Newsletter?
doesn't look like I've received it... I'll check our mail filters... Sometimes they go on the rampage and junk all kinds of stuff.
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17071551
>> see www.ee-stuff.com
Thanks. It seems our filters were deleting it. At least that's sorted now.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question