January 25, 2010 at 2:19 am
Table's name Emp_Details
emp_id emp_Birth_Date
10011969-01-21
10021974-08-21
10031982-03-14
i need the first emp_id (1001) take the second emp_Birth_date (1974-08-21), and the second Id (1002) taking the third details (1982-03-14) etc. with a SQL Update table statement.
January 25, 2010 at 2:42 am
You asked almost the same question a couple of weeks ago:
http://www.sqlservercentral.com/Forums/Topic843394-338-1.aspx
Was there a problem with that solution that we can help you with?
BrainDonor.
January 25, 2010 at 2:52 am
yes, the answer was a query (select) statement not a script (update). i need an update statement.
January 25, 2010 at 7:20 am
The update statement is different, but the thinking behind it that solved the previous question is very similair.
You can update a field in a table with a subquery - using another select statement to obtain a value.
e.g.
UPDATE #TABLE
SET #TABLE.Field1 = (SELECT value FROM #TABLE AA WHERE....)
Now knowing this, give it a try, post your code here and we'll see if you need any further help with it.
BrainDonor.
January 25, 2010 at 8:01 am
You can easily change a SELECT to an update.
SELECT a.ID, b.Test
from MyTable A
inner join MyOtherTable B
on a.ID = b.ID
where a.customer = 'Test'
you move it to an update with the same FROM/WHERE
from MyTable A
inner join MyOtherTable B
on a.ID = b.ID
where a.customer = 'Test'
Then you add an UPDATE, and you can use an alias in there.
update a
from MyTable A
inner join MyOtherTable B
on a.ID = b.ID
where a.customer = 'Test'
Lastly, add in your SET clause where you set values.
update a
set a.SalesDate = b.SalesDate
from MyTable A
inner join MyOtherTable B
on a.ID = b.ID
where a.customer = 'Test'
January 25, 2010 at 8:10 am
Steve Jones - Editor (1/25/2010)
You can easily change a SELECT to an update.Lastly, add in your SET clause where you set values.
update a
set a.SalesDate = b.SalesDate
from MyTable A
inner join MyOtherTable B
on a.ID = b.ID
where a.customer = 'Test'
A far more elegant solution than mine.
BrainDonor.
January 25, 2010 at 8:19 am
Just to refine the previous snippet;
UPDATE #TABLE
SET #TABLE.Field1 = (SELECT emp_id-1 as emp_id, emp_birth_date as Field1
from table)
January 26, 2010 at 2:15 am
hi SSChampion
can't you write the script using my table example? I do not know where u getting the "a" (first update statement)from. please use my example.
thanks
Chuene.
January 26, 2010 at 2:20 am
hi,
cant u use my table example? where are u getting the "a" (the first update statement)from ?
January 26, 2010 at 2:31 am
hi,
cant you use an example using my table details? I do understand alias but not in the manner used here.
thanks.
January 26, 2010 at 2:36 am
cmoloto-996602 (1/26/2010)
hi,cant u use my table example? where are u getting the "a" (the first update statement)from ?
Its called a Table Alias, and is a way of making SQL code more readable, and easier to type.
the line from MyTable A
allows you to refer to 'a' instead of 'MyTable'. So 'a.id' is actually the column 'MyTable.id'.
Using a table alias also allows you to refer to the same table more than once - you could have MyTable as 'A' joining onto Mytable as 'B' and you could then distinguish the data as 'A.id' and 'B.id' FROM MyTable A
LEFT OUTER JOIN MyTable B ON a.id = b.id - 1
and know which 'aspect' of the same table the data is from.
January 26, 2010 at 2:47 am
Employee Table ---Table's name
emp_Id emp_birth_date
1001 1978-04-25
1002 1982-03-14
1003 1991-02-20
please reply using my table example.
thanks.
January 26, 2010 at 2:51 am
Employee Table ---Table's name
emp_Id emp_birth_date
1001 1978-04-25
1002 1982-03-14
1003 1991-02-20
please reply using my table example.
thanks.
January 26, 2010 at 2:56 am
ci_employee ---tables name
emp_id emp_birth_date
1001 1978-04-25
1002 1982-03-14
1003 1991-02-20
pls use my table example.
thanks.
January 26, 2010 at 3:30 am
cmoloto-996602 (1/26/2010)
pls use my table example
Your table example eh? Why didn't you say so!!!
Your table name, no alias (as such)...not easy...not pretty...but:
USE tempdb;
GO
CREATE TABLE dbo.Emp_Details (emp_id INTEGER PRIMARY KEY, emp_Birth_date DATETIME NULL);
INSERT dbo.Emp_Details (emp_id, emp_Birth_date)
VALUES (1001, '19690121'),
(1002, '19740821'),
(1003, '19820314');
WITH Emp_Details
AS (
SELECT emp_id,
emp_Birth_date
FROM dbo.Emp_Details
)
UPDATE dbo.Emp_Details
SET emp_Birth_date =
(
SELECT emp_Birth_date
FROM Emp_Details
WHERE emp_id = dbo.Emp_Details.emp_id + 1
);
SELECT emp_id, emp_Birth_date
FROM dbo.Emp_Details;
DROP TABLE
dbo.Emp_Details;
Sigh.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply