select columns from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select columns from two tables

Author

Topic

RX840
Starting Member

9 Posts

Posted - 2008-12-16 : 22:20:41

I need some help with a query statement.

I want to select the Salary column from my Personnel"table1" where the XClassification column from Job"table2" equals 5, and then increase the salary column on table1 by 10%.

I am so new to this that I think i need to use some type of sub select statement, but I am not having any luck.

Any suggestions would be greatly appreciated.

Thanks!!

RX840

Patrick

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-16 : 23:06:30

Could You Please Post Your Tables structure(definition)

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 23:29:34

[code]
UPDATE t1
SET t1.Salary=t1.Salary *1.1
FROM table1 t1
INNER JOIN table2 t2
ON t1.field=t2.field
WHERE t2.Job=5
[/code]
field represents field by which tables are linked

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 23:41:40

try this

UPDATE t1
SET t1.Salary=t1.Salary *1.1
FROM table1 t1 WHERE t1.field in (select field from table2 where job = 5 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 23:48:43

[code]UPDATE t1
SET t1.Salary=t1.Salary *1.1
FROM table1 t1
WHERE EXISTS (select 1 from table2
where field=t1.field
and job = 5 )[/code]

RX840
Starting Member

9 Posts

Posted - 2008-12-17 : 01:11:36

I made a change which might help make this easier. I am trying to update all "Employee table salaries by 10 percent" who's "job table EeoCl" is LIKE Office/Clerical.

CREATE TABLE Job
(
JobId [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY (JobId),
JobTitle VARCHAR(75) NULL,
JobDesc VARCHAR(75) NULL,
ExStatus VARCHAR(10) NULL,
EeoCl VARCHAR(25) NULL,
JobCo INT NULL,
);

CREATE TABLE Employee
(
EmpId [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY (EmpId),
UNIQUE (EmpId),
JobId INT NULL,
LName VARCHAR(25) NULL,
FName VARCHAR(25) NULL,
Address VARCHAR(75) NULL,
City VARCHAR(25) NULL,
State CHAR(2) NULL,
Zip VARCHAR(10) NULL,
TelNum CHAR(15) NULL,
HireDate SMALLDATETIME NULL,
Salary DECIMAL (10,2),
Gender CHAR(1) NULL,
Age VARCHAR(3) NULL,
FOREIGN KEY (JobId) references Job(JobId)
);

Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:14:53

[code]UPDATE t1
SET t1.Salary=t1.Salary *1.1
FROM Employee t1
WHERE EXISTS (select 1 from Job
where JobId =t1.JobId
and EeoCl= '5' )
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-17 : 01:15:13

UPDATE t1
SET t1.Salary=t1.Salary *1.1
FROM Employee t1 WHERE t1.JobId in (select JobId from Job where EeoCl = 5 )
try this

RX840
Starting Member

9 Posts

Posted - 2008-12-17 : 01:18:25

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:23:22

[code]UPDATE t1
SET t1.Salary=t1.Salary *1.1
FROM Employee t1
WHERE EXISTS (select 1 from Job
where JobId =t1.JobId
and EeoCl LIKE 'Office/Clerical%' )
[/code]

RX840
Starting Member

9 Posts

Posted - 2008-12-17 : 01:33:34

visakh16 -- your submission works!! thank you very much

Does t1 stand for table1 Employee? if so could I have written Employee in each spot where you placed t1?

Thank you!

Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:36:19

quote:
Originally posted by RX840

visakh16 -- your submission works!! thank you very much

Does t1 stand for table1 Employee? if so could I have written Employee in each spot where you placed t1?

Thank you!

Patrick

Yup t1 is alias for table Employee. you can write employee itself instead of it. but its better to use alias (short name) for clarity and easiness rather than repeating long table names everywhere.

RX840
Starting Member

9 Posts

Posted - 2008-12-17 : 01:39:27

I will try to use more alias - I am very new at SQL and this has been very helpfull - thanks again for all the support!

Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:52:34

you're welcome

its always a good practice to use aliases for tables in query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources