Insert into assistance - new SQL user 1

[AnotherAlan]

Hi All,

I'm new to this forum and have a very rudimentary knowledge of SQL, i'm a Unix Admin by trade...
We have just taken on another application though which requires some sql skills above and beyond my "current" capability.

I need to insert fixed values into a table , based upon the record meeting certain condition(s) which are referenced from another field in a separate table.

i.e
Table A has fields sdi_code and cog_code, these are the fields which will have the values set.
Table A has a field called sdi_num, this is the common link between Table A and Table B.
Table B has fields called type_code and trad_code.

This is what I've tried after lots of reading and googling(without success);

Insert into Table A
set sdi_code = "yes",
cog_code = "gb"
where sdi_num in
(select sdi_num from Table B s
where s.type_code = "EUC" and s.trad_code = "DEP")
and b_code = "ron"

It should be changing multiple rows, but its not doing anything.

All help appreciated.
Thanks

 

[AnotherAlan]

I seem to be attempting to use a where clause with an insert into, which apparently isn't allowed.

Hmmm.

 

[jaxtell]

changing or inserting multiple rows? I think you want an UPDATE

UPDATE Table A
set sdi_code = "yes",
cog_code = "gb"
where sdi_num in 
(select sdi_num from Table B s
where s.type_code = "EUC" and s.trad_code = "DEP")
and b_code = "ron"

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[AnotherAlan]

Hi jaxtell,

Thanks for the reply. Unfortunately it is an insertion that I need, I am adding rows to the table not just updating any existing entries.

Cheers

 

[jaxtell]

Maybe you want something like

INSERT INTO tableA(sdi_code,cog_code,sdi_num) select 'yes', 'gb' sdi_num from tableB tb where tb.type_code = 'EUC' and tb.trad_code = 'DEP' and tb.b_code = 'ron'

My syntax might be a bit off. I don't use MySQL much these days. Your example seems to be a cross between an insert and an update. If the above statement isn't what you're looking for, can you describe what you're trying to do. Provide a few records from TableB and what the resulting records in TableA should look like.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[AnotherAlan]

Thanks Jaxtell, apologies for late reply, I'll give it a go.

Appreciate the assistance.
Alan