SQL INSERT row based on another row with same column value

Question

SNO | BOARD        | PLAYER_ID     | SIGN
1   | xyz          | 1             | X
2   | xxx          | 1             | O

I have created a similar table for the game tic tac toe and I want to insert row based on another row with the same column value.

For example:

If I send BOARD = xyz and PLAYER_ID = 2 then MySQL should consider SIGN = O

I can use ENUM to only accept 2 values for the SIGN column to accept X or O values (Suggest me for better ideas).

Possible duplicate of How to use 'select ' in MySQL 'insert' statement — Joshua R.
– Joshua R., Commented Feb 4, 2018 at 20:51
@JoshuaR. In the link, you mentioned it will insert the same value from another row. But in my case, it should toggle between X and O. — Sathvik C
– Sathvik C, Commented Feb 4, 2018 at 20:59
It sounds like you want UPDATE rather than INSERT. INSERT makes a new row. — Joshua R.
– Joshua R., Commented Feb 4, 2018 at 21:03
@JoshuaR. I don't want it with UPDATE. When I am inserting a new row, it should toggle the sign column value from the other row sign column value. — Sathvik C
– Sathvik C, Commented Feb 5, 2018 at 0:40

Joshua R. · Accepted Answer · 2018-02-05 01:16:37Z

4

Find the INSERT syntax here:

https://dev.mysql.com/doc/refman/5.7/en/insert.html

their example

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

If you combine that with an IF function in the SET part of the above UPDATE, see:

https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

e.g.

IF(SIGN='X,'O','X')

you should be able to toggle pieces for players and games.

You'll also need some way to identify the most recent move. If SNO is intended to be a unique incrementing move identifier, MAX(SNO)+1 can be used to increment it when you add rows.

edited Feb 5, 2018 at 1:16

answered Feb 4, 2018 at 21:11

Joshua R.

2,3021 gold badge18 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

SQL INSERT row based on another row with same column value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related