How to add static value when doing INSERT INTO with SELECT in a MySQL query?

Question

I have two MySQL tables a and b with fields x and y. Table b has 1 extra field z. Table a is in database db1 and b is in db2. I want to copy x and y from a to b and set a static value for z. How can I do that ?

db1.a.x -> db2.b.x
db1.a.y -> db2.b.y
4 -> db2.b.z

So far I have:

"INSERT INTO db2.b (x,y) SELECT x,y FROM db1.a"

How do I set db2.b.z to 4 ? I do not want to set a permanent default variable for the table.

xdazz · Accepted Answer · 2012-01-30 04:26:00Z

91

SELECT 4 will give you 4, so try:

INSERT INTO db2.b (x,y,z) SELECT x,y,4 FROM db1.a

answered Jan 30, 2012 at 4:26

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

htmlcoderexe Apr 26 at 19:54

Since this is the top result on Google: you can do strings, too, but not if they match a table or a column or any such identifier, even when quoted.

pilcrow · Accepted Answer · 2012-01-30 04:25:41Z

16

INSERT INTO db2.b (x, y, z) SELECT x, y, 4 FROM db1.a;

answered Jan 30, 2012 at 4:25

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

Collectives™ on Stack Overflow

How to add static value when doing INSERT INTO with SELECT in a MySQL query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related