insert and join

[An7hony]

I have some mysql

 

UPDATE t1 LEFT JOIN t2 ON t2.id = t1.id SET t1.col1 = newvalue WHERE t2.id IS NULL;

 

I have used the above example and it works well.

 

I Know need to join and insert. Have Tried the below

 

INSERT INTO Carer_Category_Subgroup LEFT JOIN Carer_Project_SubGroup ON Carer_Project_SubGroup.CPC_Carer = Carer_Category_Subgroup.CCatS_Carer SET Carer_Category_Subgroup.CCatS_Category = '50' WHERE Carer_Project_SubGroup.CPC_Project = '1';

 

but get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN Carer_Project_SubGroup ON Carer_Project_SubGroup.CPC_Carer = Carer_Cat' at line 1"

 

 

Can't seem to get this to work?

[An7hony]

looking at it i'll try this:

 

SELECT a.CPC_Project, b.CCatS_Category FROM  Carer_Project_SubGroup as a, Carer_Category_Subgroup as b Where a.CPC_Carer = b.CCatS_Carer AND a.CPC_Project = '1';

INSERT INTO Carer_Category_Subgroup (CCatS_Category) VALUES ('50');

[vinny42]

 

looking at it i'll try this:

 

You souldn't try things out, you should read about what you are doing and fix your mistake.

 

You cannot LEFT JOIN during an insert because you are creating a new record so there is no record to join yet.

 

Perhaps you should start by saying what you want to achieve, there mist be a reason why you wanted to use the JOIN?

[An7hony]

hello vinny

 

i need to insert a value into table 1 where table2 value = 1

 

can you help?

[An7hony]

think i have it

 

insert into table2 (col1, col2, col3)
select (1, 'norman', 'US') from Table1 t1
where t1.id=1 and t1.name = 'norman' and t1.country = 'US'

[An7hony]

going to try this

 

create a new field in Carer_Project_SubGroup called CPC_CatGroup. Update all CPC_Project=1 with CPC_CatGroup=50 and then run:

insert into Carer_Category_Subgroup (CCatS_Carer)
select (50) from Carer_Project_SubGroup
where Carer_Project_SubGroup.CPC_Project=1 AND Carer_Project_SubGroup.CPC_CatGroup=50

[vinny42]

 

think i have it

 

Yup, that can work. I don't know if it's what you want or need, but it can work.

 

 

going to try this

 

Go ahead, I don't know what you're trying to do so goodluck!

[An7hony]

thanks vinny