Solved

SQL Query help!

Posted on 2006-07-10
22
234 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 80
SQL Error in WHERE Clause 5 39
SQL - SP needs a little help 9 20
Help Parsing a String with SQL Syntax 23 26
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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now