Solved

SQL Query help!

Posted on 2006-07-10
22
235 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
  • 6
  • 3
  • 3
  • +2
22 Comments
 
LVL 142

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 142

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 142

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 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