March 6, 2009 at 1:14 am
Hi,
I have a subquery which would return multiple records.
I need to update another table with these multiple records.
Say table has Name, age, birthdate etc .. as columns
and .. table2 has emp_name and emp_age
my sub query is like ..
select a.emp_name from table2 a, table b where
a.emp_age = b.age
which will return multiple rows..
For this same where clause i need to update table.Name
with the table2.emp_name obtained from the above mentioned query ..
i tired some queries like this..
UPDATE table t1
SET (Name) = (
SELECT emp_name
FROM table2 t2
WHERE t2.emp_age = t1.age );
but didnt work..!!
Please help..
March 6, 2009 at 1:56 am
manju_gallivant (3/6/2009)
Hi,I have a subquery which would return multiple records.
I need to update another table with these multiple records.
Say table has Name, age, birthdate etc .. as columns
and .. table2 has emp_name and emp_age
my sub query is like ..
select a.emp_name from table2 a, table b where
a.emp_age = b.age
which will return multiple rows..
For this same where clause i need to update table.Name
with the table2.emp_name obtained from the above mentioned query ..
i tired some queries like this..
UPDATE table t1
SET (Name) = (
SELECT emp_name
FROM table2 t2
WHERE t2.emp_age = t1.age );
but didnt work..!!
Please help..
Seems simple update and no need subquery!
Try this one:
UPDATE TABLE1
SET TABLE.NAME = TABEL2.EMP_NAME
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.AGE = TABLE2.EMP_AGE
Hope it helps what do you want!
Anyway... you should post the structure of your tables and some sample data to be clear you problem and for the others who post the solution for you to be more easy writing the code and to help you faster!
Post the tables structure, add some sample data then tell what do you want to do, how to you want to see that data!
March 6, 2009 at 2:18 am
Other thing I like add apart from what Dugi has said -
Use ANSI JOINS instead of old where join syantax. You can still get the corect result with old join syntax for inner joins, but you won't able to do a OuTER JOINS in sql server 2005 or later versions.
John Smith
March 6, 2009 at 2:24 am
i thought it to keep it simple..
Let me give a full description..
I have two tables.. TIDLGGLS and TIDLRREP.
Among 29 columns in TIDLGGLS .. Few of concern..
TRANSLATED_TRM_160 VARCHAR2(3000 BYTE) NOT NULL,
TRANS_TERM_SIZE NUMBER(3) DEFAULT 0 NOT NULL,
REPOSITORY_KEY CHAR(10 BYTE) NOT NULL
BASE_LANGUAGE_TERM VARCHAR2(3000 BYTE) NOT NULL,
And in TIDLRREP
REPOSITORY_KEY CHAR(10 BYTE) NOT NULL,
BASE_LANGUAGE_TERM CHAR(79 BYTE) NOT NULL,
TRANSLATED_TERM_79 CHAR(79 BYTE) NOT NULL,
TRANS_TERM_SIZE NUMBER(3) NOT NULL,
MAX_TERM_SIZE NUMBER(3) NOT NULL,
This is the query i used for updating TIDLRREP.
UPDATE TIDLRREP t
SET (t.translated_term_79) =
( SELECT a.TRANSLATED_TRM_160
FROM TIDLGGLS a
WHERE upper(a.base_language_term) = upper(t.base_language_term) AND
t.MAX_TERM_SIZE >= a.TRANS_TERM_SIZE)
this works fine when the no of records it handles in less..
We are handling some 80k records.
After few records it says cannot insert null to translated_term_79 ..
There are no null values in TRANSLATED_TRM_160 also..
Please help..
March 6, 2009 at 2:33 am
If you were to write a SELECT against these two tables which returned just the rows you want to update from the target table, along with the target column and also the column from the source table providing the new value, then you would be almost there. Like this:
-- check first using SELECT
SELECT t1.age, t1.[name], t2.emp_name
FROM
t1
INNER JOIN table2 t2
ON t2.emp_age = t1.age
-- SELECT returns correct rows & values, so
-- ok to run UPDATE
UPDATE t1 SET [name] = t2.emp_name
FROM
t1
INNER JOIN table2 t2
ON t2.emp_age = t1.age
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2009 at 3:24 am
Hi Hope it helps..
CREATE TABLE TBL_NAMES(
NAMES VARCHAR(50),
AGE INT,
BIRTH_DATE DATETIME
)
CREATE TABLE EMPLOYEE(
EMP_NAME VARCHAR(50),
EMP_AGE INT
)
INSERT INTO TBL_NAMES VALUES('Senthil',21,GETDATE());
INSERT INTO TBL_NAMES VALUES('Kumar',23,GETDATE());
INSERT INTO TBL_NAMES VALUES('Mathan',29,GETDATE());
INSERT INTO TBL_NAMES VALUES('John',25,GETDATE());
INSERT INTO EMPLOYEE VALUES('Senthil Nathan',21)
INSERT INTO EMPLOYEE VALUES('Kumara Raghavan',23)
INSERT INTO EMPLOYEE VALUES('Vel',63)
SELECT * FROM TBL_NAMES
SELECT * FROM EMPLOYEE
UPDATE TBL_NAMES SET TBL_NAMES.NAMES = EMPLOYEE.EMP_NAME
FROM TBL_NAMES JOIN EMPLOYEE ON EMPLOYEE.EMP_AGE = TBL_NAMES.AGE
Thanks,
nathan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply