Solved

SQL Query help!

Posted on 2006-07-10
22
238 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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