Vasi04
asked on
Insert into Table3 Select from table1, table2
I need sql code for one time insertion for this condition. Thanks so much in advance
-------
Table1
id, code
1, a11,
2, x22,
3, x33,
4, z11,
5, b11,
Table2
id, txt1, txt2, txt3,
1, fsfd, dffdf, df43
2, eer, wer, erf
3, 943f, f0jf, 9ef,
4, efe, efr4, 34r
5, 43rr, 45fr, 45fr
Table3
key, txt1, txt2, txt3
--------------------------
1. I need to SELECT rows from table2 where table2.id = table1.id AND where first character in table1.code = x, y or z
2. Insert these rows in table3 and insert values in column "key" = k1
3. Then SELECT rows from table2 where table2.id = table1.id AND where first character in table1.code != x, y or z
4. Again insert these rows in table3 and insert values in column "key" = k2
I guess I need to use substring in my where clause to get first charatcer?
-------
Table1
id, code
1, a11,
2, x22,
3, x33,
4, z11,
5, b11,
Table2
id, txt1, txt2, txt3,
1, fsfd, dffdf, df43
2, eer, wer, erf
3, 943f, f0jf, 9ef,
4, efe, efr4, 34r
5, 43rr, 45fr, 45fr
Table3
key, txt1, txt2, txt3
--------------------------
1. I need to SELECT rows from table2 where table2.id = table1.id AND where first character in table1.code = x, y or z
2. Insert these rows in table3 and insert values in column "key" = k1
3. Then SELECT rows from table2 where table2.id = table1.id AND where first character in table1.code != x, y or z
4. Again insert these rows in table3 and insert values in column "key" = k2
I guess I need to use substring in my where clause to get first charatcer?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI Bill
Does the code insert new rows in table3?
Does the code insert new rows in table3?
Yep,
I suggest you run the select part -
"select
case when left(code,1) in ('x','y','z') then 'k1' else 'k2' end,
txt1, txt2, txt3
from table1 join table2 on table1.id = table2.id "
initially.
If you like what you see, then add the "insert into Table3 (key, txt1, txt2, txt3)" bit to the front of it, and it will insert these rows into the table for you.
and for the record, mcmonap's queries will do the same thing, so at a minimum you should split the points, or feel free to accept his/her answer.
I suggest you run the select part -
"select
case when left(code,1) in ('x','y','z') then 'k1' else 'k2' end,
txt1, txt2, txt3
from table1 join table2 on table1.id = table2.id "
initially.
If you like what you see, then add the "insert into Table3 (key, txt1, txt2, txt3)" bit to the front of it, and it will insert these rows into the table for you.
and for the record, mcmonap's queries will do the same thing, so at a minimum you should split the points, or feel free to accept his/her answer.
Don't worry about a split - reward the points to the answer that works for you! Thanks though BillAn1
ASKER
May be you both want to help me on this:
I want to delete all rows in table3 where key = ' ' (single space)
Delete * from table3 where key = ' ' ??
Thanks
I want to delete all rows in table3 where key = ' ' (single space)
Delete * from table3 where key = ' ' ??
Thanks
Delete from table3 where key = ' '
ASKER
Bill, I am using your solutions. I am testing. Can I get back to you on this?