October 4, 2011 at 8:19 pm
Hello,
I have:
- table1 (table1.id1, table1.code1, table1.connection_field)
- table2 (table2.id2, table2.Data, table2.connection_field)
I have to update table2.Data field based on value from table1.code1.
Is it possible with case statement or some other easier ':-) way?
Thanks,
Brano
October 4, 2011 at 10:57 pm
u can use like this.
update table1 set table1.col1=table2.col1
from table1 t1 inner join table2 t2 on t1.id=t2.id
October 5, 2011 at 4:31 am
Just to explain ghanshyam.kundu’s post (table names are creating confusion):
update table1 set table1.col1=table2.col1
from table1 t1 inner join table2 t2 on t1.id=t2.id
table1 – the table you want to update. In your case ‘table2’
table2 – the table you want to update from. In your case ‘table1’
table1.col1 – The column you want to update in Table1. In your case ‘table2.Data’
table2.col1 – The column you want to update from Table 2. In your case ‘table1.code1’
t1.id=t2.id – The link between table1 and Table2.
October 5, 2011 at 6:13 am
Thank you guys, will try, after I match this with my tables :-).
October 5, 2011 at 6:27 am
thanks dev to clarify it.
October 5, 2011 at 6:34 am
I must ask one more question:
How to use CASE statement (or similar, maybe if?),
because it is not just Update, it is based on data from table2 another values in table1.
October 5, 2011 at 6:40 am
based on the link (and any additional conditions in WHERE clause).
t1.id=t2.id – The link between table1 and Table2.
October 5, 2011 at 6:58 am
branovuk (10/5/2011)
I must ask one more question:How to use CASE statement (or similar, maybe if?),
because it is not just Update, it is based on data from table2 another values in table1.
Please will you post some table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and expected results, because I haven't got any idea what you're trying to do here.
Also, the method posted by ghanshyam.kundu can give unexpected results if the table you're updating from has more than one matching row for each row in the table you're updating. I'll be interested to see your sample data to see whether this is the case. Also, is there a foreign key constraint between the two tables? Please script that out and post it if there is. To avoid the danger of unexpected results, you might consider using the MERGE statement.
John
October 5, 2011 at 6:58 am
Thank you,
I solved with CASE statement.
October 5, 2011 at 7:00 am
branovuk (10/5/2011)
I must ask one more question:How to use CASE statement (or similar, maybe if?),
because it is not just Update, it is based on data from table2 another values in table1.
You may be looking for something like this:
update table1 set table1.col1=CASE WHEN table2.col1 IS NULL THEN 1 ELSE table2.col1 END
from table1 t1 inner join table2 t2 on t1.id=t2.id
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 5, 2011 at 7:07 am
branovuk (10/5/2011)
Thank you,I solved with CASE statement.
It's traditional, when people have gone to the trouble to help you, to post the solution you ended up with. This will also help anyone else who has the same problem in the future.
I suspect that you don't understand the danger of cardinality errors when you use the UPDATE...FROM syntax (this is what I was describing in my previous post) and so if you can spare the time to post the sample data and any foreign key constraint, then we can have a look and check whether this is likely to affect you.
Thanks
John
October 5, 2011 at 7:39 am
Hi john,
I tried (actually, I tested it first) next:
UPDATE table1
SET table1.DataId =
(
CASE
WHEN table2.Code2 = cond1 THEN 1
WHEN table2.Code2 = cond2 THEN 2
WHEN table2.Code2 = cond3 THEN 3
WHEN table2.Code2 = cond4 THEN 4
WHEN table2.Code2 = cond5 THEN 5
WHEN table2.Code2 = cond6 THEN 6
WHEN table2.Code2 = cond7 THEN 7
WHEN table2.Code2 = cond8 THEN 8
WHEN table2.Code2 = cond9 THEN 9
ELSE ''
END
)
FROM table1 INNER JOIN table2 ON table1.Id = table2.Id AND table2.Extra_Condition = 'something'
And it worked. Still, I wonder is there anything can go wrong?
Thanks,
Brano
October 5, 2011 at 7:50 am
And new data are:
IdCode2 DataId
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
233cond11
074cond22
074cond22
074cond22
074cond22
074cond22
074cond22
074cond22
074cond22
074cond22
175cond33
175cond33
175cond33
175cond33
175cond33
175cond33
175cond33
175cond33
276cond11
276cond11
276cond11
276cond11
276cond11
276cond11
276cond11
276cond11
377cond22
377cond22
377cond22
377cond22
377cond22
377cond22
Where id is connection between tables.
October 5, 2011 at 7:52 am
branovuk (10/5/2011)
Still, I wonder is there anything can go wrong?
Brano
Unfortunately, there is, because if UPDATE...FROM causes cardinality errors then there is no error message. Consider the situation where table2 has more than one row for each value of Id in table1. You can find out whether this is the case like so:
SELECT t1.Id,
COUNT(*) AS NoofRows
FROM table1 t1
JOIN table2 t2 ON t1.Id = t2.Id
WHERE t2. Extra_Condition = 'something'
GROUP BY t1.Id
HAVING COUNT(*) > 1
Now, if that returns any rows, you're in trouble, because you don't know which one of the rows in table2 will have its Code2 used in the CASE expression. Even if there are no rows returned by the above, how do you know your data won't change in future?
If all that sounds a bit abstract, post the DDL and sample data in the form of INSERT statements and I'll give you a concrete example.
John
October 5, 2011 at 10:34 am
It took some times :-), but here is example:
CREATE TABLE table1
(
key1 int,
connected_column varchar(255),
DataId varchar(255),
)
INSERT INTO table1
(key1, connected_column, DataId)
VALUES
(1, 'id1', 'test'),
(2, 'id2', 'test'),
(3, 'id2', 'test'),
(4, 'id2', 'test'),
(5, 'id3', 'test'),
(6, 'id3', 'test'),
(7, 'id4', 'test'),
(8, 'id5', 'test'),
(9, 'id6', 'test'),
(10, 'id10', 'test')
CREATE TABLE table2
(key2 int,
connected_column varchar(255),
Code2 varchar(255),
Extra_Condition varchar(255)
)
INSERT INTO table2
(key2, connected_column, Code2, Extra_Condition)
VALUES
(1, 'id1', '1-10', 'something'),
(2, 'id2', '11-20','something'),
(3, 'id3', '21-30','something'),
(4, 'id4', '31-40','something'),
(5, 'id5', '41-50','nothing'),
(6, 'id6', '51-60','something'),
(7, 'id7', '61-70','something'),
(8, 'id8', '71-80','something'),
(9, 'id9', '81-90','something'),
(10, 'id10', '11-20','anything'),
(11, 'id11', '11-20','nothing'),
(12, 'id12', '11-20','anything'),
(13, 'id13', '41-50','something'),
(14, 'id14', '41-50','nothing'),
(15, 'id15', '81-90','something')
Update statement:
UPDATE table1
SET table1.DataId =
(
CASE
WHEN table2.Code2 = '1-10'
THEN 1
WHEN table2.Code2 = '11-20'
THEN 2
WHEN table2.Code2 = '21-30'
THEN 3
WHEN table2.Code2 = '31-40'
THEN 4
WHEN table2.Code2 = '41-50'
THEN 5
WHEN table2.Code2 = '51-60'
THEN 6
WHEN table2.Code2 = '61-70'
THEN 7
WHEN table2.Code2 = '71-80'
THEN 8
WHEN table2.Code2 = '81-90'
THEN 9
END
)
FROM table1 INNER JOIN table2 ON table1.connected_column = table2.connected_column AND table2.Extra_Condition = 'something'
It looks like it is giving good results?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply