Solved

SQL Query help!

Posted on 2006-07-10
22
233 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

13 Experts available now in Live!

Get 1:1 Help Now